Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

paulyeo11
Valued Contributor II

SET Expression does not able show amount for previous year ?

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

1 Solution

Accepted Solutions
chinnakatikisg
Valued Contributor

Re: SET Expression does not able show amount for previous year ?

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

3 Replies
paulyeo11
Valued Contributor II

Re: SET Expression does not able show amount for previous year ?

My QV doc

chinnakatikisg
Valued Contributor

Re: SET Expression does not able show amount for previous year ?

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
Valued Contributor II

Re: SET Expression does not able show amount for previous year ?

Hi Ven

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

Paul

Community Browser