Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello guys,
I have a date in my app called 'application date'. I want to be able to show revenue for the last 6 working days broken by down by different product sold. As you can see in the below image, the dates go back to 06/30/2021 when I am viewing this sheet on 07/06.
I want to see these dates : 06/25 and 06/28 - 07/02 when I view on 07/06. (last six working days)
07/03, 07/04 and 07/05 were holidays in the US.
Here is how you would do this using set analysis (even though I agree with @avinashelite that this is best implemented as a flag in a calendar)
Sum({<[Application Date]={">$(=FirstWorkDate(Today(), 6, '07/03/2021','07/04/2021','07/05/2021'))"}>}Revenue)
Better still, store the holidays in a variable...
Sum({<[Application Date]={">$(=FirstWorkDate(Today(), 6, $(vHolidays)))"}>}Revenue)
Note that this assumes your workdays are Monday through Friday.
Try like this
1. Create a master calendar .
2. Create a flag in the master calendar to identify the holidays
3. In the set analysis using the above flag to eliminate the holidays
Here is how you would do this using set analysis (even though I agree with @avinashelite that this is best implemented as a flag in a calendar)
Sum({<[Application Date]={">$(=FirstWorkDate(Today(), 6, '07/03/2021','07/04/2021','07/05/2021'))"}>}Revenue)
Better still, store the holidays in a variable...
Sum({<[Application Date]={">$(=FirstWorkDate(Today(), 6, $(vHolidays)))"}>}Revenue)
Note that this assumes your workdays are Monday through Friday.
This works well. Thanks!