Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

SET Expression for getting Jan 2016 amount ?

Hi All

I have below expression will return CYTD ( Jan and Feb ) amount , is working fine :-

money(

sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} $(ColumnDim51)/$(Columndim89)/1000)

, $(vMoneyFormatK))

May i know how to modify the above expression , so that it will return CY ( Jan ) Amount ?

Paul

1 Solution

Accepted Solutions
sunny_talwar

Seems like Feb's sum is equal to 0 and hence this below formula would show the same number for Jan as it was showing for Jan and Feb. But if there was some data for Feb, then the below expression will work:

money(

sum({$<year = {$(=max(year))}, month = {"$(=max({<year={$(=max(year))}>} month)-1)"}>} $(ColumnDim51)/$(Columndim89)/1000)

, $(vMoneyFormatK))

View solution in original post

5 Replies
sunny_talwar

Seems like Feb's sum is equal to 0 and hence this below formula would show the same number for Jan as it was showing for Jan and Feb. But if there was some data for Feb, then the below expression will work:

money(

sum({$<year = {$(=max(year))}, month = {"$(=max({<year={$(=max(year))}>} month)-1)"}>} $(ColumnDim51)/$(Columndim89)/1000)

, $(vMoneyFormatK))

paulyeo11
Master
Master
Author

Hi Sunny

Thank you vey much , your above exmpression is working fine .

Same as above , i hope you can help me modify the below expression , to display Last year Jan 2016 Amount:-

money(

sum({$<year = {$(=max(year)-1)}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>}$(ColumnDim51)/$(Columndim89)/1000)

, $(vMoneyFormatK))

Paul

sunny_talwar

Try this:

Money(

Sum({$<year = {$(=max(year)-1)}, month = {"$(=max({<year={$(=max(year))}>} month)-1)"}>} $(ColumnDim51)/$(Columndim89)/1000)

, $(vMoneyFormatK))

paulyeo11
Master
Master
Author

Hi Sunny

Thank you very much for your help , it work.

Paul

sunny_talwar

Awesome