Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
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

Re: YTD - Set Analysis Exclusion

Try this:

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

3 Replies
Partner
Partner

Re: YTD - Set Analysis Exclusion

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)

Re: YTD - Set Analysis Exclusion

Try this:

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

Not applicable

Re: YTD - Set Analysis Exclusion

Works !!!! Thank you!!