Set Analysis

[Zaga_69]

Hi,

I am again struggling with the set analysis in Qlik 😕

I would like to replace  in the following formula the '201701'  by the the last 12 months:

sum({1<YearMonth={201701}>}Product_Sales) // itworks 

To calculate the last 12 months I used:

date(addmonths((date#(max(YearMonth),'YYYYMM')),-12),'YYYYMM')  /it works

However, when trying to replace 201701 by the last 12 months formula above, it did not work...

Any idea?

Thanks

P.S: YearMonth it is field from my Fact table. I used "1" identifier because I wanted to ignore all selections 

[dplr-rn]

My understanding is that you want all data from a year ago till max year

Try below.  

sum({1<YearMonth={">=$(date(addmonths((date#(max(YearMonth),'YYYYMM')),-12),'YYYYMM'))"}>}Product_Sales)

[jlongoria]

This is not a direct answer, but I think reading/reviewing the link below will give ideas on how to proceed. I found this extremely helpful when I was working on a project that needed analysis of data over time periods: 

https://qlikviewcookbook.com/2012/01/easy-period-analysis-using-qlikview-components/

[Anil_Babu_Samineni · Accepted solution]

If you want last year of same month, Just use like

sum({1<YearMonth={"$(=date(addmonths((date#(max(YearMonth),'YYYYMM')),-12),'YYYYMM'))"}>}Product_Sales)

If you want last year of same month to this this month, Just use like

sum({1<YearMonth={">=$(=date(addmonths((date#(max(YearMonth),'YYYYMM')),-12),'YYYYMM'))<=$(=date(=Max(YearMonth, 'YYYYMM')))"}>}Product_Sales)

[Zaga_69]

Hi,

Thank you! the first expression was what I was exactly looking for 🙂

In the he second one, I did not get your idea.

One comment: 

the following formula when nothing is selecting in the Filter Pane (YearMonth) it brings the sales for the maximum (YearMonth - 12). Is there anyway when a period is selecting to show nothing or zero?

sum({1<YearMonth={"$(=date(addmonths((date#(max(YearMonth),'YYYYMM')),-12),'YYYYMM'))"}>}Product_Sales)

Best regards,

Edi

[Zaga_69]

I realized that just deleting the "max" function of the formula, it brings 0 when selecting nothing in the filter pane.

[Anil_Babu_Samineni]

I apologies for late reply, Glad we were able to help and found your self.