SET Expression does not able show amount for previous year ?

[paulyeo11]

Hi All

I have a Table , i am using below expression, is working fine :-

sum({$<year = {$(=max(year) - $(=if(max(month) = 1, 1, 0)))}, month = {$(=num(month(addmonths(max(YearMonth), -1))))}>}  Invoice_Amt_SGD/1000)

My issue is when the number increase to 7 , it display null :-

sum({$<year = {$(=max(year) - $(=if(max(month) = 1, 1, 0)))}, month = {$(=num(month(addmonths(max(YearMonth), -7))))}>}  Invoice_Amt_SGD/1000)

Can some one advise me how to check where it the error ?

Paul

[paulyeo11]

My QV doc

[Anonymous · Accepted solution]

Dear Paul,

Your expression is returning Dec 2014 as year condition your extracting is returning 2014.

Please use the following expression:

sum({$<year = {$(=num(year(addmonths(max(YearMonth), -7))))}, month = {$(=num(month(addmonths(max(YearMonth), -7))))}>}  Invoice_Amt_SGD/1000)

Change your other caluclations also according to the above expression. Hope this helps..

Please mark this as correct, if your result is as expected.

Regards,

Venkat

[paulyeo11]

Hi Ven

Thank you very much for your help , with out it i will not be able to figure out.

Paul