Making use of calendar for weekly quotas

[jonbowles84]

Hi Guys,

I have my appsheet set up nicely with everything working how I would like, but I feel it could possibly be improved by making better use of the calendar UX, I'm just not sure how best to go about it...

I have a weekly quota to fulfill from midnight on Sunday to midnight on Sunday. and the quota quite often changes week to week. At the moment I have all data in one table and I have a slice that filters based on a "from and to date" value. So each week I manually change the dates to check I made the quota.

What do you think would be the best way to accurately reflect this information, without having to manually change dates each week? Also with the quota, i am just running a tally adding eeach weeks number to the previous total which gives me an overall figure, and is more manual than it should be...
Any tips would be awesome

[Joseph_Seddik · Accepted solution]

Hi Jon,

You can setup a dynamic slice that will always show you the data over a week. This week can be based on a sliding window of days, or starting from a fixed weekday which I understand is your case. 

When you say "from midnight on Sunday to midnight on Sunday", then you'll have 8 days not a week.

For the purpose of explanation I'll assume that your start on Monday at "00:00" and you want to include all the data throughout the week up to the whole Sunday; this is a full week. I will base my expressions based on this assumption, and you should be able to easily adjust it if needed. 

1. Calculating dates: 

End date:

This should be the date of the last Sunday that can also be today. You can use this expression to calculate that date: 

TODAY() - WEEKDAY(TODAY()) + 1

Start Date:

This should be the date of the preceding Monday, for this you can use the following expression:

TODAY() - WEEKDAY(TODAY()) + 1 - 6
(or just: -5) 

Now, no matter when you open the corresponding view for the slide, you'll always have last weeks data. 

2. Calculating the Tally

Let's give the slice we've created the name: "lastWeek". This slice contains the column "amount" which is the column we would sum to calculate last week's tally, using this expression:

SUM(lastWeek[amount])

Now, somewhere else you have a "totalTally", that you need to update once every week with the tally of last week. There's more than one way. I would do the following:

1. Create a new slice and name it thisWeek, which will include the records starting from Monday this week, using this expression for its row filter condition: 
   
   [row date] >= TODAY() - WEEKDAY(TODAY()) + 2
   
   
2. Create an action to update the totalTally column. Its expression should be: 
   
   [totalTally] + SUM(lastWeek[amount])
   
   
3. Create a bot to launch this action triggered by the creation of a new record in thisWeek  slice. However, the bot would launch the action only if the triggering row is the only row in the slice, using this expression for the condition: 
   
   1 = COUNT(thisWeek[_RowNumber])

That's all!

[jonbowles84]

Thanks @Joseph_Seddik , this is awesome!!
I have the slices set up, I think your "tally" is the number of fullfilled orders which is a sum of the number of entries between the date range, is that right?
The weekly quota may be 50 one week and 200 the next just depending on how the client feels that week, so I have a column "Weekly Quota" and a column "Total Quota" and an action that adds "Weekly Quota" to "Total Quota" every Monday @ 00:01, hopefully that should work?
Thanks again for your help!!

[Joseph_Seddik]

I have the slices set up, I think your "tally" is the number of fullfilled orders which is a sum of the number of entries between the date range, is that right?

Honestly I don't know 🙂 I just re-used your word. But yes it IS supposed to be a sum, I just don't know it is a sum of what. A sum of orders is perfectly OK. 

action that adds "Weekly Quota" to "Total Quota" every Monday @ 00:01, hopefully that should work?

---

Yes as a timed action it should work. Here the trigger is a certain DateTime every week, and this would be a correct alternative to my point number 3. 

In my point number 3. I used a different trigger which is the creation of the first record the next week. 

Just be aware that you should use either trigger but NOT both of them, otherwise you will have your total updated twice.

Also, if you use your method, do not put the condition that I included; it is valid only with the use of record creation, not time, as a trigger. 

Welcome!!

[jonbowles84]

Ho would I have the pie chart set up dynamically to refelect the "this week', "lastweek" slices? Its currently just a total like this: 

[Joseph_Seddik]

Just create two new Views in UX, and link each to the corresponding slice using For this data field in the View.

[jonbowles84]

I havent had any luck with this so have left it in the "too hard basket" until now! 
Could you please help me with what im doing wrong here... I have the sheet like this now:

And in this table there are also 2 VC's with references to another table that should be changing fairly often ("Credits" and "Delivered"). I then have an action as follows:

And a bot with an "all changes" event and this expression:

OR(
[_THISROW_BEFORE].[Credits]<>[_THISROW_AFTER].[Credits],
[_THISROW_BEFORE].[Delivered]<>[_THISROW_AFTER].[Delivered]
)

Which runs the action, however it does nothing... Any help?? 😊