topic set analysis with criteria before max date in QlikView

set analysis with criteria before max date

h_demarco — Fri, 14 Oct 2016 11:33:32 GMT

I have a balance sheet dashboard with all accounting statements and a initial balance of each G/L account.

When I filter dates, I want the set analysis to sum initial balance + all statements BEFORE maximum selected date.

[Data] is the date field and to get maximum date I use

=Max(Data)

This is working fine.

But to sum values

<= Max(Data)

in set analysis is not working.

It should be something like below formula as I saw in many related discussions, but it is not working.

=initial_balance+sum({<Data={"<=$(=max(Data))"}>} AMOUNT)

Any idea on how to correctly write this set analysis sentence?

Re: set analysis with criteria before max date

sunny_talwar — Fri, 14 Oct 2016 16:58:19 GMT

May be you need to use only less than < instead of less than equal to <=

=RangeSum(initial_balance, Sum({<Data={"<$(=max(Data))"}>} AMOUNT))

Also do you make any selection in date related field which might contradict your set analysis? like selection in Month field? If you do, then you need to ignore that in your set analysis

Re: set analysis with criteria before max date

h_demarco — Mon, 31 Oct 2016 15:02:04 GMT

It worked. In fact, error was in contradicted selection. Once we ignored it, correct sum showed up.

Thanks