3 Replies Latest reply: Apr 21, 2016 12:22 PM by Satish Kurra RSS

    KPI calculation error

      I am somewhat new to Qlik Sense and not really a code guy, but typically I can figure things out by looking at forums such as this.


      What I am trying to accomplish is a KPI that shows sales for the month. 


      When I do this formula (for the year), everything works out fine and I get my yearly total.

      Sum({<CALENDAR_YEAR={$(=vCurrentYear)}>} SumOfGROSS_SALE_AMT)


      However, when I do this, I get nothing.

      Sum({<CALENDAR_YEAR={$(=vCurrentYear)},{NUM(MONTH(TRANS_DATE))={$(=CurrentMonthNum)}>} SumOfGROSS_SALE_AMT)


      Note, I have in my data load editor the following


      LET vCurrentMonthNum = NUM(Month(Makedate(2016,3,31)));

      LET vCurrentMonth = Month(Makedate(2016,3,31));

      LET vCurrentYear = NUM(year(Makedate(2016,3,31)));


      Can anyone help me see what I may be doing wrong?

        • Re: KPI calculation error
          Sunny Talwar

          I would suggest you to create a Month field in the script and then may be try this:


          Sum({<CALENDAR_YEAR={$(=vCurrentYear)}, MONTH_FIELD={$(=CurrentMonthNum)}>} SumOfGROSS_SALE_AMT)



          • Re: KPI calculation error
            Gysbert Wassenaar
            LET vCurrentMonthNum = NUM(Month(Makedate(2016,3,31)));

            That's a rather complex way to basically do this: LET vCurrentMonthNum = 3;


            To answer your real question:

            You can only use real field names on the left side of the = in a set analysis expression. What you should do is first create a Month field in the data load editor:



                 ...lots of fields,

                 Num(Month(TRANDATE)) as CALENDAR_MONTHNUM

            FROM ...


            Then your expression can be changed to

            Sum({<CALENDAR_YEAR={$(=vCurrentYear)},CALENDAR_MONTHNUM={$(=vCurrentMonthNum)}>} SumOfGROSS_SALE_AMT)

            • Re: KPI calculation error
              Satish Kurra

              Try this


              Sum({<CALENDAR_YEAR={$(=vCurrentYear)},NUM(MONTH(TRANS_DATE))={$(=CurrentMonthNum)}>} SumOfGROSS_SALE_AMT)