Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How to use KPI to compare this month KPI to last month KPI dynamically?

Hi,

I have a KPI figure for revenue and I want to add another measure for last month revenue. How would I accomplish this? How might I change the color of this month revenue and glyphs when this month revenue > last month or < last month?

Thank you so much.

Tags (2)
18 Replies

Re: How to use KPI to compare this month KPI to last month KPI dynamically?

Depends how your expression look like but usually is Month(Date)-1

With your second question

You can use(example)11-Jun-15 10-42-17 AM.jpg

Not applicable

Re: How to use KPI to compare this month KPI to last month KPI dynamically?

Month(Date)-1 gives me last month. So if this month is June (6) then last month is 5. How can I input this information and output revenue for last month? Sorry I'm a bit new to Qlik sense.

santhoo_san
Contributor II

Re: How to use KPI to compare this month KPI to last month KPI dynamically?

Hi Check if this helps

Negative.JPGPositive.JPG

Not applicable

Re: How to use KPI to compare this month KPI to last month KPI dynamically?

Hi Santhosh,

How can I get previous month to dynamically appear as a KPI? Example: select Dec -> Nov sales appears as KPI, select June -> May sales appears as KPI.

santhoo_san
Contributor II

Re: How to use KPI to compare this month KPI to last month KPI dynamically?

you can use an expression like below

Pr Month : Sum({<DateID={$(=max(DateID)-1)}>}Qv_Amount)

Cur Month : Sum({<DateID={$(=max(DateID))}>}Qv_Amount)

Var :

Sum({<DateID={$(=max(DateID))}>}Qv_Amount)

/Sum({<DateID={$(=max(DateID)-1)}>}Qv_Amount)

under variance you can select conditional colors and define the range for colors

caio_caminoski
Contributor

Re: How to use KPI to compare this month KPI to last month KPI dynamically?

Allen, If you want to work with monthes, you should creat a field 'Month/Year' in your script.

You can do that by adding a field in your table (scrpit) like:

Monthname([Date Field]) as MonthYear

Then you can add in your KPI for previous month:

sum( {< MonthYear = {'$(=monthname(addmonths(max(MonthYear), -1)))'}  >} Amount )

Actual Month:

sum( {< MonthYear = {'$(=monthname(max(MonthYear)))'}  >} Amount )




The variation will be:

( sum( {< MonthYear = {'$(=monthname(max(MonthYear)))'}  >} Amount )

-

sum( {< MonthYear = {'$(=monthname(addmonths(max(MonthYear), -1)))'}  >} Amount ) )

/

sum( {< MonthYear = {'$(=monthname(addmonths(max(MonthYear), -1)))'}  >} Amount )


Not applicable

Re: How to use KPI to compare this month KPI to last month KPI dynamically?

Hi Allen if you are using qliksense try extension deltaviz.

deltaViz self service dashboard

you can just put expression for this month and last month and this will calculate variance by itself. I have not used this extension for this scenario but maybe it can tried.

satishkurra
Valued Contributor II

Re: How to use KPI to compare this month KPI to last month KPI dynamically?

Hi

See if the below link can help you to an extent.

https://www.youtube.com/watch?v=k90u-nNRQFA

Not applicable

Re: How to use KPI to compare this month KPI to last month KPI dynamically?

can you please explain how the '+' and '-'  symbol is displaying next to the count.