How to compare the last month with the previous regardless of the selection

[Anonymous]

Dear , I have read several articles on comparison periods but can not find a simple way to find the solution to my problem. I know it's a simple formula but I can not find it.

To explain my fields : MES for the month , MONTO for the sale.

So, I have all the selected Qlik year , but I want to make a single sum of sales for the last month , I mean the most recent month. Tall so that when you update data more recent months this formula always bring me the last month.

A formula that works but that should change every month would be this:

Sum({<MES={jun}>} MONTO)     -     here I am indicating to bring me the sum of sales for June (Jun ) but I want that it to bring me the last month, so I will not update for each change of month

I am very grateful that I can help .

[TKendrick20]

To make sure I understand your question, you want the dashboard to show the sales for the most recent month and the month before the most recent month?

I would recommend not using the text values for the months, but instead a Month_Key. So 201501 would represent January of 2015 and 201608 would represent August of 2016.

Then you can do something like this:

Most recent month: SUM({<Month_Key=MAX(Month_Key)> MONTO)

Previous month: SUM({<Month_Key=MAX(Month_Key)-1>} MONTO)

Of course, you will have to do some extra error-checking if the month is January so that it correctly shows the previous month as December of the previous year.

[jpenuliar]

Hi Maximiliano,

I think you wanted to achieve a Month on Month comparison.

Please see below from Lee Mathews

QlikView How To (or Tips & Tricks) Application | Qlik Community

[TKendrick20]

Oh, and to disregard all other selections in the app use:

SUM({1<Month_Key=MAX(Month_Key)> MONTO)

[Anonymous]

Hi Tim, thanks for your time, I tell you that tried these measures but I did not recognize it as validity , there is an error , I think that is about Month_Key and if I put what you say 201501 , it would be the same that says " jun".

If another method please let me know it 's wrong, I'll keep trying

Thank you very much

[TKendrick20]

The Month_Key is not a standard field. You will need to build this in your data load before you can use it in your visualization. Share your app file if you are able!

[Anonymous]

Tim, how load in the script the field "Month_Key" ? .

[TKendrick20]

Convert your month name to the month number, then append it to the end of the year.

So, wherever you are loading the month names, add this line to the load script:

Year & NUM(MONTH(DATE#(Mes,'MMM')),'00') as Month_Key,

Where Year, and Mes are fields that you are already loading.