6 Replies Latest reply: Jan 13, 2017 1:55 PM by Sunny Talwar RSS

    Translate Monthly YTD figures to actual Month figures

    Adam Klaum

      Hello Qlik gurus!

       

      I have some data that I'm working with that stores a metric with a financial figure per month.  So it looks something like this (very simplified)

       

      MetricDateFigure
      Metric 1Aug 201610
      Metric 1Sep 201625
      Metric 1Oct 201635

       

       

      The problem is that the number in the Figure column is cumulative for the year.  So in this example the actual month figure for Oct 2016 was not 35, it was 10.  10 for Aug, 15 for Sep and 10 for Oct adds up to 35.  I need to get the actual month figures for each month by subtracting the figure for the previous month from the target month.  So the output I want would be this:

       

       

       

      MetricDateFigure
      Metric 1Aug 201610
      Metric 1Sep 201615
      Metric 1Oct 201610

       

      I know this could be fairly easily done on the presentation side,  but these numbers are used in so many places in the final dashboard that I would rather just convert the data once on the ETL side.  I've thought about looping through the original table and doing a lookup for each row to find the previous month figure.  However, this is several hundred thousand rows, and that seems really intensive.  I was looking into "Preceding Loads" as well, but can't wrap my head around how I might use it in this case.

       

      Any other thoughts or suggestions on how I might accomplish this?  Many thanks.

        • Re: Translate Monthly YTD figures to actual Month figures
          Sunny Talwar

          May be using Peek()/Previous()

           

          Table:

          LOAD Metric,

            Date(Date#(Date, 'MMM YYYY'), 'MMM YYYY') as Date,

            Figure;

          LOAD * INLINE [

              Metric, Date, Figure

              Metric 1, Aug 2016, 10

              Metric 1, Sep 2016, 25

              Metric 1, Oct 2016, 35

          ];

           

          FinalTable:

          LOAD Metric,

            Date,

            Figure,

            If(Metric = Previous(Metric) and Date = AddMonths(Previous(Date), 1), RangeSum(Figure, -Peek('Figure')), Figure) as NonCumFigure

          Resident Table;

           

          DROP Table Table;

           

          Capture.PNG

            • Re: Translate Monthly YTD figures to actual Month figures
              Sunny Talwar

              And looking at Oleg's response made me realize that second condition in if might have to removed or changed based on the data and requirement when a month is not available in between)

               

              Table:

              LOAD Metric,

                Date(Date#(Date, 'MMM YYYY'), 'MMM YYYY') as Date,

                Figure;

              LOAD * INLINE [

                  Metric, Date, Figure

                  Metric 1, Aug 2016, 10

                  Metric 1, Sep 2016, 25

                  Metric 1, Oct 2016, 35

              ];

               

              FinalTable:

              LOAD Metric,

                Date,

                Figure,

                If(Metric = Previous(Metric) and Date = AddMonths(Previous(Date), 1), RangeSum(Figure, -Peek('Figure')), Figure) as NonCumFigure

              Resident Table;

               

              DROP Table Table;

            • Re: Translate Monthly YTD figures to actual Month figures
              Oleg Troyansky

              Hi Adam,

               

              you go it right, looping through your data could be extremely slow. However, you can do the same using the functions peek() and Previous(). The two functions are similar, yet slightly different (look them up in the Help sections).

               

              If you reload your data using a Resident load and sort the data in the correct chronological order, then you can reach into the previously loaded row and get the previous number of the Figure and subtract it from the current value of the Figure to calculate the current value:

               

              IF( Peek('Metric') = Metric, Figure - Previous(Figure), Figure) as NetFigure

               

              You can learn more about Peek() vs. Previous() from a variety of sources, including my book QlikView Your Business.

               

              cheers,

              Oleg Troyansky

              Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Munich, Germany!

              • Re: Translate Monthly YTD figures to actual Month figures
                Andrey Khoronenko

                Hi Adam

                 

                Try, if the script

                 

                Directory;

                LOAD Metric,

                     Date,

                     rangesum (Figure,-Previous(Figure)) as Figure

                FROM....

                 

                Regards

                Andrey

                • Re: Translate Monthly YTD figures to actual Month figures
                  Adam Klaum

                  Thank you!  I had no idea that Previous existed.  So handy.  However, now I have created a new problem for myself.  Let's say the new example data is as follows:

                   

                   

                  MetricDateFigure
                  Metric 1Aug 201610
                  Metric 1Sep 20160
                  Metric 1Oct 20160
                  Metric 1Nov 201625
                  Metric 1Dec 201635

                   

                   

                  The problem comes when I hit the Nov record.  If it just examines the previous month then evaluates 25 - 0, but the desired output is as follows:

                   

                   

                  MetricDateFigure
                  Metric 1Aug 201610
                  Metric 1Sep 20160
                  Metric 1Oct 20160
                  Metric 1Nov 201615
                  Metric 1Dec 2016

                  10

                   

                  Is there a way to use Previous to go back an arbitrary number of records until one is found without a zero value?  I know this seems silly since it's a cumulative YTD figure (should be a figure each month), but unfortunately, if there has been no additional numbers for a month, they report nothing.

                    • Re: Translate Monthly YTD figures to actual Month figures
                      Sunny Talwar

                      May be this:

                       

                      Table:

                      LOAD Metric,

                        Date(Date#(Date, 'MMM YYYY'), 'MMM YYYY') as Date,

                        Figure;

                      LOAD * INLINE [

                          Metric, Date, Figure

                          Metric 1, Aug 2016, 10

                          Metric 1, Sep 2016, 0

                          Metric 1, Oct 2016, 0

                          Metric 1, Nov 2016, 25

                          Metric 1, Dec 2016, 35

                      ];

                       

                      FinalTable:

                      LOAD Metric,

                        Date,

                        Figure,

                        NewFigure,

                        If(Metric = Previous(Metric) and Date = AddMonths(Previous(Date), 1), RangeSum(NewFigure, -Peek('NewFigure')), NewFigure) as NonCumFigure;

                      LOAD Metric,

                        Date,

                        Figure,

                        If(Metric = Previous(Metric) and Date = AddMonths(Previous(Date), 1), If(Figure = 0, Peek('NewFigure'), Figure), Figure) as NewFigure

                      Resident Table;

                       

                      DROP Table Table;

                       

                      Capture.PNG