5 Replies Latest reply: Aug 13, 2018 10:28 AM by Bala Bhaskar RSS

    Sum 12 months data according to the selected period

    Diego Alejandro Velez Becerra

      Hi, good day to all of you.

       

      I'm trying to do a KPI in which I need to have 2 variables, the first one with the sum of the "Past due portfolio" field for the selected period and the other one having the accumulative sum of "sales" field for the the full year before the selected period.

       

      How can I do the second one, I'm using a filter that restricts that users can only select one period.

       

      Thanks a lot, I know it seems easy but I'm kinda new to Qlik Sense.

       

      Cordially,

      Diego Vélez

      Qlik sense student.

        • Re: Sum 12 months data according to the selected period
          Bala Bhaskar

          May be this:

          aggr(RangeSum(Above(Sum({<Date = {">=$(=Max(Date) - 11)<=$(=Max(Date))"}>} Units),0,12)), $(Var_Selected),Date)

           

          Check this link:

          Calculating rolling n-period totals, averages or other aggregations

            • Re: Sum 12 months data according to the selected period
              Diego Alejandro Velez Becerra

              Hi Bala Thanks a lot for your answer,

               

              I learnt a lot reading the topic you shared with me, and tried to use your formula and some of the formulas in the other post, they worked somehow but were also giving some trouble to me when I was trying to show the data as I wanted to, also my period format "MMYYYY" and the fact that I needed to sum the full 12 months rolling, including this year and the previous one were also problematic at some moment.

               

              So I ended up doing a formula that included all of the periods one by one with some kind of variations.It's not practical but did the trick.

               

              Sum({<PERIODO={$(=Max(PERIODO))}>} [VALOR_CONSUMO_ACTIVA])

              sums the selected period


              +

              if

              (

              mid(Max({$} [PERIODO]),5,2) -1 <= 0,

              Finds out if the previous PERIOD to the selected one is in this year (ex for 201801 is 01 and the previous PERIOD would be 201712)

              Sum({<PERIODO={$(=(Max(PERIODO)-89))}>} [VALOR_CONSUMO_ACTIVA]),

              if the previous PERIOD to the selected one is in the past year adds the value for this PERIOD , this situation only happens when the selected period is YYYY01 then the difference will always be 89 and will grow by 1 with each period on the formula.

              Sum({<PERIODO={$(=(Max(PERIODO)-1))}>} [VALOR_CONSUMO_ACTIVA])

              if the previous PERIOD to the selected one is in this year then just rests 1 to the selected PERIOD.

              )

               

              and it keeps going until the 12 PERIODS.

              +

              if

              (

              mid(

              Max({$} [PERIODO]),5,2) -2 <=0,

              Sum({<PERIODO={$(=(Max(PERIODO)-90))}>} [VALOR_CONSUMO_ACTIVA]),

              Sum({<PERIODO={$(=(Max(PERIODO)-2))}>} [VALOR_CONSUMO_ACTIVA])

              )

               

              +

              if

              (

              mid(

              Max({$} [PERIODO]),5,2) -3 <=0,

              Sum({<PERIODO={$(=(Max(PERIODO)-91))}>} [VALOR_CONSUMO_ACTIVA]),

              Sum({<PERIODO={$(=(Max(PERIODO)-3))}>} [VALOR_CONSUMO_ACTIVA])

              )


              I believe the formula could be shorter using a growing variable but I ain't familiar with that yet.


              Thanks a lot again.

            • Re: Sum 12 months data according to the selected period
              Anil Samineni

              Perhaps this?

               

              Sum(Aggr(If(DateField>=Date(AddMonths(DateField,-11)) and DateField<=Date(Max(DateField)),Sum({<PERIODO={$(=Max(PERIODO))}>} [VALOR_CONSUMO_ACTIVA])

              +

              if

              (

              mid(Max({$} [PERIODO]),5,2) -1 <= 0,

              Sum({<PERIODO={$(=(Max(PERIODO)-89))}>} [VALOR_CONSUMO_ACTIVA]),

              Sum({<PERIODO={$(=(Max(PERIODO)-1))}>} [VALOR_CONSUMO_ACTIVA])

              )

              +

              if

              (

              mid(

              Max({$} [PERIODO]),5,2) -2 <=0,

              Sum({<PERIODO={$(=(Max(PERIODO)-90))}>} [VALOR_CONSUMO_ACTIVA]),

              Sum({<PERIODO={$(=(Max(PERIODO)-2))}>} [VALOR_CONSUMO_ACTIVA])

              )

              +

              if

              (

              mid(

              Max({$} [PERIODO]),5,2) -3 <=0,

              Sum({<PERIODO={$(=(Max(PERIODO)-91))}>} [VALOR_CONSUMO_ACTIVA]),

              Sum({<PERIODO={$(=(Max(PERIODO)-3))}>} [VALOR_CONSUMO_ACTIVA])

              )),Dimension1,Dimension2))

                • Re: Sum 12 months data according to the selected period
                  Diego Alejandro Velez Becerra

                  Hi Anil, sorry for not answering before I was in a little trip, I don't really understand the last answer you gave, I was thinking in something like

                   

                  Sum({<PERIODO={$(=Max(PERIODO))}>} [VALOR_CONSUMO_ACTIVA])

                  +

                  For i = 1 to 11

                  if

                  (

                  mid(

                  Max({$} [PERIODO]),5,2) -i <=0,

                  Sum({<PERIODO={$(=(Max(PERIODO)-(88+i)))}>} [VALOR_CONSUMO_ACTIVA]),

                  Sum({<PERIODO={$(=(Max(PERIODO)-i))}>} [VALOR_CONSUMO_ACTIVA])

                  )


                  Maybe you can help me to define that variable for that part of the formula so I can give you the correct answer, because you helped me a lot. Thanks again.