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

[#1]

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!

[shraddha_g]

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

[teiswamsler]

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

[#4 · Accepted solution]

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