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

# 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

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
LET vCurrentMonthNum = NUM(Month(Makedate(2016,3,31)));

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

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

Try this

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