topic Month to Date Set Analysis in QlikView

Month to Date Set Analysis

Mon, 15 Nov 2010 08:54:14 GMT

Hi guys!

I need your help regarding on Month to Date using set analysis. I have this code for my MonthToDate computation:

sum({<PAID_DATE={">=$(=date(MonthStart(Max(PAID_DATE)))) <=$(=Max(PAID_DATE))"}, Year=, Month=, Day=>} PAIDAMOUNT)

My question is, What will be my set analysis for the following :

1.) Previous Month with the same date (example: 11/15/2010 selection should compute = from 10/01/2010 to 10/15/2010)

2.) Full Month Two Months Ago (example: 11/15/2010 selection should compute = from 09/01/2010 to 09/30/2010)

3.) Full Month Three Months Ago (example: 11/15/2010 selection should compute = from 08/01/2010 to 08/31/2010)

Or If you have any suggestion or work around or easier approach regarding my problem, it will be very much appreciated.

Thanks in advance!

Regards,

Lester

Month to Date Set Analysis

pover — Mon, 15 Nov 2010 18:49:59 GMT

Lester,

Try the following for each option:

1)sum({<PAID_DATE={">=$(=date(MonthStart(Max(PAID_DATE),-1))) <=$(=addmonths(Max(PAID_DATE),-1))"}, Year=, Month=, Day=>} PAIDAMOUNT)
//most month functions have a month offset parameter you can use and if not use addmonths()

2)sum({<PAID_DATE={">=$(=date(MonthStart(Max(PAID_DATE),-2))) <=$(=date(MonthEnd(Max(PAID_DATE),-2)))"}, Year=, Month=, Day=>} PAIDAMOUNT)

3)sum({<PAID_DATE={">=$(=date(MonthStart(Max(PAID_DATE),-3))) <=$(=date(MonthEnd(Max(PAID_DATE),-3)))"}, Year=, Month=, Day=>} PAIDAMOUNT)

Regards.

Month to Date Set Analysis

Tue, 16 Nov 2010 02:50:32 GMT

Thank you very much Karl!!! Those 3 set analysis works..

Regards,
Lester

Month to Date Set Analysis

Thu, 02 Dec 2010 10:48:33 GMT

Hi Karl,

I have a follow-up question:

What will be my Set Analysis for Yesterday using this Month To Date Set Analysis?

sum({<PAID_DATE={">=$(=date(MonthStart(Max(PAID_DATE)))) <=$(=Max(PAID_DATE))"}, Year=, Month=, Day=>} PAIDAMOUNT)

Thanks in advance!

Lester

Month to Date Set Analysis

pover — Thu, 02 Dec 2010 16:50:25 GMT

Try this...

sum({<PAID_DATE={">=$(=date(MonthStart(Max(PAID_DATE)))) <=$(=today()-1)"}, Year=, Month=, Day=>} PAIDAMOUNT)

Regards.