topic Re: Set Analysis in QlikView

Set Analysis

Zaga_69 — Fri, 14 Dec 2018 17:51:22 GMT

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

Re: Set Analysis

dplr-rn — Fri, 14 Dec 2018 18:35:59 GMT

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)

Re: Set Analysis

jlongoria — Fri, 14 Dec 2018 21:11:29 GMT

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/

Re: Set Analysis

Anil_Babu_Samineni — Mon, 17 Dec 2018 08:43:19 GMT

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)

Re: Set Analysis

Zaga_69 — Wed, 19 Dec 2018 10:30:20 GMT

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

Re: Set Analysis

Zaga_69 — Wed, 19 Dec 2018 10:46:22 GMT

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

Re: Set Analysis

Anil_Babu_Samineni — Thu, 20 Dec 2018 03:36:20 GMT

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