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: 
Not applicable

YTD - Set Analysis Exclusion

Below is a working formula that I am using to get the SUM of my Previous Year:

=sum({$<YEAR= {$(vPrevYear)}, [Acc Month] = {"<=$(=max({<[Acc Year]={$(=max([Acc Year]))}>} [Acc Month]))"} >} Line_Pending_Count)

My issue is that I have a filter box using the [YEAR] field listing the current years in the data (2016, 2015). When "2016" is selected in the filter box the formula above is correct, but when "2015" is selected it is taking the SUM of the entire year (2015). I know this is because [Acc Year] is being modified when making a selection to the [YEAR] filter box. So for example: if I select [YEAR] = 2015... the MAX([Acc Year] is now 2015.....

My question is how do I override this in the formula above? I would like max[Acc Year] to always be the TRUE max. I know you can place a {1} in front of the field, but I can't seem to get that to work in the particular formula.

Any suggestions would be greatly appreciated!

Thanks

1 Solution

Accepted Solutions
sunny_talwar

Try this:

=Sum({$<YEAR= {$(vPrevYear)}, [Acc Month] = {"<=$(=max({1<[Acc Year]={$(=max({1}[Acc Year]))}>} [Acc Month]))"}>} Line_Pending_Count)

View solution in original post

3 Replies
krishnacbe
Partner - Specialist III
Partner - Specialist III

Try Adding the selection exclusion for the [Acc Year] field.

Hope below expression helps

=sum({$<[Acc Year]=, YEAR= {$(vPrevYear)}, [Acc Month] = {"<=$(=max({<[Acc Year]={$(=max([Acc Year]))}>} [Acc Month]))"} >} Line_Pending_Count)

sunny_talwar

Try this:

=Sum({$<YEAR= {$(vPrevYear)}, [Acc Month] = {"<=$(=max({1<[Acc Year]={$(=max({1}[Acc Year]))}>} [Acc Month]))"}>} Line_Pending_Count)

Not applicable
Author

Works !!!! Thank you!!