Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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/
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)
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
I realized that just deleting the "max" function of the formula, it brings 0 when selecting nothing in the filter pane.
I apologies for late reply, Glad we were able to help and found your self.