7 Replies Latest reply: Jan 17, 2017 10:32 AM by Rahul Gaadhe RSS

    Qlik Sense Line chart calculation

    Rahul Gaadhe

      Dear Experts,

       

      I am trying to achieve a functionality in Qlik Sense Line chart's expression.

      Attached excel have sample data which I am trying to achieve.

      Little bit of explanation below :

       

      I have data upto March 2016 at month level (Column D).

      I want to calculate New Amount (Column E) based on Column I "Initial New Amount" and "Increment Amount".

      I am able to calculate "Initial New Amount" and "Increment Amount" based on certain business rules but I am not able to figure out how I can calculate the "New Amount" for the missing months, formula to be used is ..

      For first missing month is = "Initial New Amount"

      Subsequent Missing Month = Previous Month New Amount + "Increment Amount"

       

      Any help in this regard is highly appreciated.

       

      Regards,

      RG

        • Re: Qlik Sense Line chart calculation
          Andrey Khoronenko

          Do I understand the problem?

           

          There is a table

           

          Download.jpg

          That needs to be downloaded in Qlik Sense. Further, we have

           

          Download1.jpg

          Must be extended to the form table

           

          Download2.jpg

          Correctly?

            • Re: Qlik Sense Line chart calculation
              Rahul Gaadhe

              Hi Andrey,

               

              Yes, but it is to be done at chart level not in script.

               

              Regards,

              RG

                • Re: Qlik Sense Line chart calculation
                  Andrey Khoronenko

                  Hi Rahul!

                   

                  On the chart level is necessary to create a synthetic dimension (by field "Date", the downloaded Date is not). Functions ValueList() and ValueLoop() as arguments accept only constants. It is not possible to create a dimension "date"calculated by.

                  As an option, exit out of this situation as follows. Likely the master calendar is loaded during download script. It is possible to include the field start of the month as a standard date. Example,

                   

                                          MonthStart(MakeDate(Year(Left([Period], 10)),Month(Left([Period], 10)))) as CalendarFistMonthDay

                   

                  This allows the field "CalendarFistMonthDay" is the master calendar used as dimension in our diagram.


                  As an option, expression of the chart could look like this


                                          If(CalendarFistMonthDay <= Max(DateS), Sum(Value), 25 + 5*(Month(CalendarFistMonthDay)-4))


                  An example can be found in the attached file.


                  Regards,

                  Andrey