18 Replies Latest reply: Nov 18, 2015 9:27 AM by Calum Stevens

# 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.

• ###### 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)

• ###### 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.

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

Hi Check if this helps

• ###### 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.

• ###### 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

• ###### 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.

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

Under Measures > Your expression > 'on' conditional colors > click on the Add limit value. you will get colors and symbols. Select the symbol you want.

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

Hi Santosh, I am trying to create exactly what you're illustrated above but I'm not getting anywhere! (I'm very new to Qlik and have been enjoying the out of the box visualisations but dont really have any experience of using expressions or even really where you put them!) I've attached a .qvf in the hope you or any other willing volunteer might have time to throw those expressions in the right places and make it sing so I can see a working demo and backwards engineer it from there! Any help much appreciated, fingers crossed

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

Hi

Is this you are looking for?

Santhosh

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

Hi Santhosh, I can't see an attachment, but maybe I'm being blind

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

here is the attachment

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

Thanks Santhosh but I don't think that's quite what I was looking for, I'm trying to create your example above that looks like this:

• ###### Re: How to use KPI to compare this month KPI to last month KPI dynamically?
, I'm trying to create your example above that looks like this:

Do you mean the %  sign?

How to: Add Country Flags (201 Flags Included)

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

No the whole thing i.e three KPIs, one reflecting the month selection, one reflecting the previous month to the month selection, and one displaying the variance.

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

Hi

if you are are looking for color and symbol change, you can do so by selecting a name from the list and if you are looking for variance%, that you need to take difference of two different values in place of existing expressions.

Santhosh

• ###### 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 )

• ###### 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.

• ###### 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.