topic Re: YTD - Set Analysis Exclusion in QlikView

YTD - Set Analysis Exclusion

Tue, 17 Jan 2017 18:45:44 GMT

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

Re: YTD - Set Analysis Exclusion

krishnacbe — Tue, 17 Jan 2017 18:52:46 GMT

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

sunny_talwar — Tue, 17 Jan 2017 18:52:57 GMT

Try this:

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

Re: YTD - Set Analysis Exclusion

Tue, 17 Jan 2017 18:58:21 GMT

Works !!!! Thank you!!