Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Set Analysis

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 

 

 

1 Solution

Accepted Solutions
Highlighted

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

6 Replies
Highlighted
Partner
Partner

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)

 

Highlighted
Creator
Creator

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/

Highlighted

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

Highlighted
Creator
Creator

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

Highlighted
Creator
Creator

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

 

Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)