3 Replies Latest reply: Oct 26, 2016 10:22 AM by Renata Ribeiro

# I need to see the comparative month to month with set analysis

Hello guys,

My KPI is not working. It does'nt  return the value according to my selection of months.

It is linked to a set analysis condition to the comparative considering only sales PROMOTIONS.

When you do not have any filter it returns me the difference between September x August (OK, that's what I want Too), but when I select August for example, it does not return any value to me, as if error in the formula. I need to see the comparative month to month depending on my selections.

Example:

selected october -  KPI shows difference (%) between october x september

selected september - difference (%) between september x august

selected augost - difference (%) between augost x july

How do I do?

App with no selection

App with  month selection

See the KPI expression:

(Sum ({<Promotion = { 'Promotion'}, year = { '\$ (Vano)'}, [Month] = { '\$ (= Date (AddMonths (Max ([Month / Year]) - 1) 'MMM')) '}>} Quantity) /

Sum ({<Promotion = { 'Promotion'}, year = { '\$ (Vano)'}, [Month] = { '\$ (= Date (AddMonths (Max ([Month / Year]), - 2),' MMM)) '}>} Amount)) - 1

Expression of my chart:

Sum ({<Promotion = { 'Promotion'}, [Month / Year] = { "> = \$ (# = AddYears (Max ([Month / Year]) - 1)) <= \$ (# = Max ([ month / Year])) "}>} Quantity)

Sorry about my english, I'm brazilian

can anyone help me?

Tks!

• ###### Re: I need to see the comparative month to month with set analysis

try with below:

(Sum ({<Promotion = { 'Promotion'}, year = { '\$ (Vano)'}, [Month] = { " (= Date (AddMonths (Max ([Month / Year]) - 1) 'MMM')) "}>} Quantity) /

Sum ({<Promotion = { 'Promotion'}, year = { '\$ (Vano)'}, [Month] = { "\$ (= Date (AddMonths (Max ([Month / Year]), - 2),' MMM')) "}>} Amount)) - 1

• ###### Re: I need to see the comparative month to month with set analysis

Hi Renata

Have made a solution to handle Month to Month comparison based on selection in Year Month field, using "Derive" function to generate an AutoCalender for "Data"

Take a look into attached qvf.

Hopefully you can implement the it in the rest of your application.

/Teis

• ###### Re: I need to see the comparative month to month with set analysis

Hi guys,

A friend from another community have found a solution:

See below:

Create two variables, considering current month x previous month:
ex.

Month/Year1 = =DATE(addmonths(max([Month/Year]),-0), 'MMM/YYYY')

Month/Year2 = =DATE(addmonths(max([Month/Year]),-1), 'MMM/YYYY')

In the variable window, do not forget to put the equal sign (=) before the expression, so Qlik will evaluate the expression off the set analysis.

KPI expression:

(Sum({<Promoção={'Promoção'},[Month/Year]={'\$(Month/Year1)'}>}sales)/

Sum({<Promoção={'Promoção'},[Month/Year]={'\$(Month/Year2)'}>}sales))-1

Thanks for the others answers anyway