Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis for Previous Month

Hello, Fellow Experts.

I know this has already been asked and below are the references:

* https://community.qlik.com/thread/222779?q=Set%20Analysis%20for%20Previous%20Month

* http://aftersync.com/blog/the-magic-of-set-analysis-point-in-time-reporting

* Set Analysis Wizard for QlikView | qlikblog.at

But it seems my formula for getting the SUM from the previous month is not working:

TRY 1:

SUM(

{$<[date.autoCalendar.YearMonth] = {"1<[date.autoCalendar.YearMonth] = {'=$(MAX([date.autoCalendar.YearMonth]) - 1)'}, [date.autoCalendar.YearMonth] = >"}, [date.autoCalendar.YearMonth] = >}

utilization_den)

TRY 2:

var_NumeralDate = =((Year(date) - 1) * 12) + Num(Month(date))

SUM(

{$<var_NumeralDate = {"1<var_NumeralDate = {'=$(MAX(var_NumeralDate) - 1)'}, var_NumeralDate = >"}, var_NumeralDate = >}

utilization_den)

It seems these formula works but still shows zero.

I may need your help if there might be problem with my syntax or something.

2 Replies
Not applicable
Author

TRY 3:

Data Load Editor:

    DATEPART(yyyy,[date]) as util_year,

    DATEPART(mm,[date]) as util_month,

    CONCAT(DATEPART(yyyy,[date]),DATEPART(mm,[date])) as util_yearmonth,

SUM(

{<util_year = ,util_month = ,util_yearmonth  = {"$(=DATE(ADDMONTHS(MAX(date),-1),'YYYYM'))"}>}

utilization_den)

Not applicable
Author

TRY 4:

I think I solved it now:

SUM(

{1<util_yearmonth  = {"$(=DATE(ADDMONTHS(MAX(date),-1),'YYYYM'))"}>}

utilization_den)