New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted Not applicable

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?

Tags (2)
1 Solution

Accepted Solutions MVP

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)

3 Replies MVP

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) MVP & Luminary

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)

talk is cheap, supply exceeds demand
Valued Contributor II

Re: KPI calculation error

Try this

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