Current YTD until last complete week vs Prior YTD until last complete week?

[Michael_TP]

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:

1. Current YTD vs Prior YTD;
2. Current YTD until last complete month vs Prior YTD until last complete month.  Eg: if I run this today, it will give me YTD until the end of February 2018. But then if I select another period it should adjust accordingly.
3. Current YTD until last complete week vs Prior YTD until last complete week. Eg: if I run this today, it should return the YTD until the end of last week complete week, ending on march 5th 2018 (week 9) and it should return the Prior YTD until end of week 9 of 2017. But then if I select another period it should adjust accordingly.

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:

• YTD - Current Year=
  SUM({<Ano={'$(vMaxYear)'},SALESDATE={'<=$(vMaxDate)'},[Mês]=,Dia=,SemanaAno=>}Quantidade)

• YTD - Prior Year
  SUM({<Ano={'$(vPriorYear)'},Mês=,Dia=,SALESDATE={'<=$(vPriorYearDate)'},SemanaAno=>}Quantidade)

• Variation YTD=
  (
  SUM({<Ano={'$(vMaxYear)'},SALESDATE={'<=$(vMaxDate)'},[Mês]=,Dia=,SemanaAno=>}Quantidade)
  -
  SUM({<Ano={'$(vPriorYear)'},Mês=,Dia=,SALESDATE={'<=$(vPriorYearDate)'},SemanaAno=>}Quantidade)
  )
  /
  SUM({<Ano={'$(vPriorYear)'},Mês=,Dia=,SALESDATE={'<=$(vPriorYearDate)'},SemanaAno=>}Quantidade)

2) Current YTD until last complete month vs Prior YTD until last complete month:

• YTD - Current Year - Last Full Month=
  sum({<SALESDATE={">=$(=Date(YearStart(Max(SALESDATE)),'DD/MM/YYYY'))<=$(=Date(MonthEnd(AddMonths(Max(SALESDATE)+1,-1)),'DD/MM/YYYY'))"},[Mês]=,Ano=,SemanaAno=>} Quantidade)

• YTD - Prior Year - Last Full Month=
  sum({<SALESDATE={">=$(=AddYears(Date(YearStart(Max(SALESDATE)),'DD/MM/YYYY'),-1))<=$(=AddYears(Date(MonthEnd(AddMonths(Max(SALESDATE)+1,-1)),'DD/MM/YYYY'),-1))"},[Mês]=,Ano=,SemanaAno=>} Quantidade)

• Variation YTD - Last Full Month=
  (
  sum({<SALESDATE={">=$(=Date(YearStart(Max(SALESDATE)),'DD/MM/YYYY'))<=$(=Date(MonthEnd(AddMonths(Max(SALESDATE)+1,-1)),'DD/MM/YYYY'))"},[Mês]=,Ano=,SemanaAno=>} Quantidade)
  -
  sum({<SALESDATE={">=$(=AddYears(Date(YearStart(Max(SALESDATE)),'DD/MM/YYYY'),-1))<=$(=AddYears(Date(MonthEnd(AddMonths(Max(SALESDATE)+1,-1)),'DD/MM/YYYY'),-1))"},[Mês]=,Ano=,SemanaAno=>} Quantidade)
  )
  /
  sum({<SALESDATE={">=$(=AddYears(Date(YearStart(Max(SALESDATE)),'DD/MM/YYYY'),-1))<=$(=AddYears(Date(MonthEnd(AddMonths(Max(SALESDATE)+1,-1)),'DD/MM/YYYY'),-1))"},[Mês]=,Ano=,SemanaAno=>} Quantidade)

Could someone help me out a bit with the 3) Current YTD until last complete week vs Prior YTD until last complete week?

• YTD - Current Year - Last Full Week?
• YTD - Prior Year - Last Full Week?
• Variation YTD - Last Full Week?

Thank you,

Michael

[chrishayes · Accepted solution]

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])

[Michael_TP]

Thank you for your suggestion. I'm new at Qlik so I will take a look at how to create and use variables.

[Michael_TP]

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.