Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Zaga_69
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
Anil_Babu_Samineni

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)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

6 Replies
dplr-rn
Partner - Master III
Partner - Master III

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
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/

Anil_Babu_Samineni

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)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Zaga_69
Creator
Creator
Author

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
Creator
Creator
Author

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.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful