How to use WeekStart or WeekEnd to show last full week
Hi, I have daily data from 1st January 2022 through to 1st October 2023, showing Date, actual and planned sales together with other information.
I wish to show in table a dimension showing week beginning dates using date from 1st Jan 2023 through to the last full week. Today's date is 29th August 2023, so the last week beginning would be 21st August 2023.
I need to ensure that when I use the sum function that the last week beginning date would sum up to and include 27th August.
Next Monday (4th September 2023), the last week beginning date would show as 28 August and sum up to and including 3rd Sept.
I have been getting into a muddle using WeekStart and WeekEnd functions in my dimension with Date.
I can't alter the load script as other reports use date pre 2023 and planned future sales sales.
The best approach here is to add a flag like a "LastWeek_Flag" to your calendar table and assign a 1/0 value to each date. This way, you can calculated Min Date with the flag=1, Max Date with the flag = 1, and sum(any value) with the Flag=1.
To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!
Thanks Oleg for your idea, though I don't know how to use the calendar table. I was hoping to use a formula in my dimension in order to show week beginning dates. In other columns use a simple sum to generate weekly data. Additionally, in another table generate months in a dimension and the sum function.
