Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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

Re: Set Analysis for Previous Month

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

Re: Set Analysis for Previous Month

TRY 4:

I think I solved it now:

SUM(

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

utilization_den)