Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
Try with this
sum({$<YEAR={$(=only(YEAR)-1)}, POST_MONTH={$(=ADDMONTHS(POST_MONTH,-1))}>}AMOUNT)
Celambarasan
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?
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)
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.
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
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
Hi,
Please refer to attachment.
Is it this that you were searching for?
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.