Set Analysis of a Date

[jrothfuss]

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

[CELAMBARASAN]

Hi,

     Try with this

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

Celambarasan

[jrothfuss]

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?

[#4]

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)

[jrothfuss]

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.

[swuehl · Accepted solution]

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

[CELAMBARASAN]

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

[#8]

Hi,

Please refer to attachment.

Is it this that you were searching for?

[jrothfuss]

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.