5 Replies Latest reply: Jul 8, 2015 5:58 AM by Sunny Talwar RSS

    Problem with Previous year to date calculation

       

      Hi all,

       

      On Qlik Sense, I have to compare YTD actuals Previous Year to Budget previous year.

       

      I have created a Master Calendar. I have created Year, Month, YearMonth fields.

       

      I have formula that does work fine for calculating YTD Actuals and YTD Budget. No problem.

       

      But I do have a problem with Previous YTD for Actuals and Budget.

       

      As long as I don’t have any Year dimension selected, my two formulas are working fine (showing 2015 for Actuals and 2014 for Previous).

       

      But as soon as I select any given Year, then the Previous YTD for Actuals and Budget don’t work anymore, giving 0 as result.

       

      Here are my 2 formulas:

       

      Previous YTD Budget =sum({$<Year_MonthNum={">=$(=max(Year)-1 & '01')  <=$(=max(Year)-1 & num(Month(today()), '00'))"}>} Budget)

       

      Previous YTD Actuals =sum({$<Year_MonthNum ={">=$(=max(Year)-1 & '01')  <=$(=max(Year)-1 & num(Month(today()), '00'))"}, Type_Mission={'Assessment'}>}Invoice)

       

      The same formulas without “-1” (to do current year and not previous) are working just fine, with or without a year selected.

       

      I feel I miss something with set modifier but despite many tries I still don’t understand what is happening (and I’m not a developer). Could someone help me on this?

       

      Thanks in advance

       

        • Re: Problem with Previous year to date calculation
          Sunny Talwar

          Are your trying them in a text box object or a chart?? For text box object you can try this:

           

          Previous YTD Budget =sum({$<Year_MonthNum={">=$(=max(Year)-1 & '01')  <=$(=max(Year)-1 & num(Month(today()), '00'))"}, Year = >} Budget)

           

          Previous YTD Actuals =sum({$<Year_MonthNum ={">=$(=max(Year)-1 & '01')  <=$(=max(Year)-1 & num(Month(today()), '00'))"}, Type_Mission={'Assessment'}, Year = >}Invoice)

           

          I would suggest adding all other calendar fields on which you might make a selection (like Month, Week, Date etc) except Year_MonthNum which is already present.

          HTH

          Best,

          Sunny