4 Replies Latest reply: Jun 21, 2018 10:53 AM by Michael Delgado RSS

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

    Michael Delgado

      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