Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm new to Qlik Sense Cloud and i am having some problems calculating YTD to last complete week. Our weeks run from Tuesday-Monday and we are using unbroken weeks.
For this project, our users want to see 3 distinct view of the YTD:
I've already used the advice from this topic YTD vs Last YTD Comparison to pull the YTD vs Prior YTD and for full months also, but I'm struggling with full week.
This is what i have so far:
1) Current YTD vs Prior YTD:
2) Current YTD until last complete month vs Prior YTD until last complete month:
Could someone help me out a bit with the 3) Current YTD until last complete week vs Prior YTD until last complete week?
Thank you,
Michael
My suggestion is to create variables to store all the different date ranges you need to put in your expression within your load script. Even better if you have a database table for your calendar and you assign a week start and a week end date there. Again still store the values in a variable in your load script. Below is what I am using for prior year to date sales in one of my apps. You can modify it to fit your other use cases by replacing the variables.
Sum({$<[Invoice Date.Calendar.Year]={$(v_prior_year)},[Invoice Date]={"<=$(v_py_ytd)"}>}[Billed Amount USD])
My suggestion is to create variables to store all the different date ranges you need to put in your expression within your load script. Even better if you have a database table for your calendar and you assign a week start and a week end date there. Again still store the values in a variable in your load script. Below is what I am using for prior year to date sales in one of my apps. You can modify it to fit your other use cases by replacing the variables.
Sum({$<[Invoice Date.Calendar.Year]={$(v_prior_year)},[Invoice Date]={"<=$(v_py_ytd)"}>}[Billed Amount USD])
Thank you for your suggestion. I'm new at Qlik so I will take a look at how to create and use variables.
Thank you for your suggestion Christopher.
I was busy with other stuffs so I couldn't get back to this.
I did what you suggested and I've created vStartDate and vEndDate variables to replace in the formula. It works like a charm and like this I only have to make changes in one place and it gets reflected on the whole app.