Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

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)

LOAD MONTH(TRANS_DATE) as MONTH_FIELD

3 Replies

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)

LOAD MONTH(TRANS_DATE) as MONTH_FIELD

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;

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:

LOAD

     ...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
satishkurra
Valued Contributor II

Re: KPI calculation error

Try this

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

Community Browser