Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

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

Capturar 3.JPG

App with  month selection

Capturar 2.JPG

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!

1 Solution

Accepted Solutions
Not applicable

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

3 Replies
shraddha_g
Not applicable

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

teiswamsler
Not applicable

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

Not applicable

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