topic Set Analysis of a Date in QlikView

Set Analysis of a Date

jrothfuss — Tue, 21 Feb 2012 17:18:49 GMT

I need to compare results from the month prior to my current selections. I have used set analysis to do this quite effectively for YEAR which is a number. My attempts to do the same thing with POST_MONTH, which is the monthstart in date format, have repeatedly failed.

This code works for YEAR:
sum({$<YEAR={$(=only(YEAR)-1)}>}AMOUNT)

This code returns the previous month date that I am interested in:
ADDMONTHS(POST_MONTH,-1)

I've read many posts here and tried dozens of different ways. How can I successfully combine the two?
Thank you.
Jared

Set Analysis of a Date

CELAMBARASAN — Tue, 21 Feb 2012 17:25:16 GMT

Hi,

Try with this

sum({$<YEAR={$(=only(YEAR)-1)}, POST_MONTH={$(=ADDMONTHS(POST_MONTH,-1))}>}AMOUNT)

Celambarasan

Re: Set Analysis of a Date

jrothfuss — Tue, 21 Feb 2012 17:36:10 GMT

Thanks for taking a look at this.

That expression returned null. Also, in this case I don't need the prior year... just the prior month (which would be the prior year when January is selected).

I tried this too (just removing the YEAR part of the expression) and it also returned null:
sum({$<POST_MONTH={$(=ADDMONTHS(POST_MONTH,-1))}>}AMOUNT)

Other thoughts?

Set Analysis of a Date

Tue, 21 Feb 2012 17:43:26 GMT

You can define a variable vMaxDate as Max(date) if you have a date field. Otherwise as makeDate(Max(Year), Max(POST_MONTH), 1)

sum({<Year = { '$(= Year(AddMonths($(vMaxDate)), -1))' }, POST_MONTH = { '$(= Month(AddMonths($(vMaxDate)), -1))'} >} AMOUNT)

Re: Set Analysis of a Date

jrothfuss — Tue, 21 Feb 2012 20:40:34 GMT

Thanks for your reply. I should probably define my question better.

In theory this expression should return the sum(AMOUNT) for the month prior to the selection, but it returns the current month value instead:

sum({$<POST_MONTH={"=$(=addmonths(only(POST_MONTH),-1))"}>}AMOUNT)

I have attached a qvw with a working example, the blue chart has the expression in it that needs help.

Re: Set Analysis of a Date

swuehl — Tue, 21 Feb 2012 20:52:43 GMT

Try this:

sum({$<POST_MONTH={'$(=addmonths(only(POST_MONTH),-1))'},Year=>}AMOUNT)

The ,Year= part is only necessary if your users may select also from year. If they would, the selection in Year and your new set POST_MONTH would get incompatible, so you need to clear the field.

Regards,

Stefan

Set Analysis of a Date

CELAMBARASAN — Wed, 22 Feb 2012 04:30:21 GMT

Hi,

Try with this

sum({$<YEAR=, POST_MONTH={$(=Month(ADDMONTHS(POST_MONTH,-1)))}>} AMOUNT)\\use this If Post_Month has month as jan,feb like that if not then look at below

sum({$<YEAR=, POST_MONTH={">=$(=MonthStart(ADDMONTHS(POST_MONTH,-1)))<=

$(=MonthEnd(ADDMONTHS(POST_MONTH,-1)))}>} AMOUNT)\\use this if it is a date

Hope it helps

Celambarasan

Re: Set Analysis of a Date

Wed, 22 Feb 2012 05:04:12 GMT

Hi,

Please refer to attachment.

Is it this that you were searching for?

Re: Set Analysis of a Date

jrothfuss — Fri, 24 Feb 2012 22:58:32 GMT

I chose to use the code recommended by swuehl because it was the shortest, Celambarasan's code did work too. Thanks again to all each of you.