topic Re: Using set analysis to filter on the date in the dimension in QlikView

Using set analysis to filter on the date in the dimension

Wed, 25 Nov 2020 16:16:04 GMT

Hi I want to use set analysis on the As Of Date in the dimension column. I am trying to count the paid amount up to a certain date.

This formula works. sum(if([Paid Date] <= [As Of Date], [Paid Amount]))

This does not. It grabs the max date of the entire set, not the dimension.

sum({<[Paid Date] ={"$(= '<=' & max([As Of Date]))"}>} [Paid Amount])

As Of Date	Paid Amount To Date
5/1/2016	$ 2,500.00
4/1/2016	$ 1,100.00
3/1/2016	$ 800.00
2/1/2016	$ 400.00

Edit for clarification:

My issue is that for sum({<[Paid Date] ={"$(= '<=' & max([As Of Date]))"}>} [Paid Amount]), max([As Of Date]) always equals 5/1/2016. I want it to equal 2/1/2016 when 2/1/2016 is the value in dimension, 3/1/2016 when 3/1/2016 is the value in dimension, and so on. I hope that clarifies my question.

Thanks in advance.

Re: Using set analysis to filter on the date in the dimension

sunny_talwar — Tue, 09 Aug 2016 16:17:31 GMT

Would you be able to share a sample to look at this?

Re: Using set analysis to filter on the date in the dimension

vishsaggi — Tue, 09 Aug 2016 16:30:57 GMT

You are missing the Set syntax I presume, try again:

sum( {< [Paid Date] = { "$(= '<=' & max([As Of Date]))" } >} [Paid Amount])

Re: Using set analysis to filter on the date in the dimension

Tue, 09 Aug 2016 17:03:53 GMT

I did have the Set syntax right in Qlikview, I just forgot to type it out correctly in my question.

Re: Using set analysis to filter on the date in the dimension

vishsaggi — Tue, 09 Aug 2016 18:30:26 GMT

Oh ok. Would it be possible to send us a sample app your are working on?

Re: Using set analysis to filter on the date in the dimension

Tue, 09 Aug 2016 20:22:55 GMT

Try this expression:

sum({<As Of Date={"$(='>=' & PaidDate)"}>}Amount)

Re: Using set analysis to filter on the date in the dimension

Tue, 09 Aug 2016 21:26:18 GMT

I did try this expression and it did not work:

sum({<As Of Date={"$(='>=' & PaidDate)"}>}Amount)

Re: Using set analysis to filter on the date in the dimension

sunny_talwar — Tue, 09 Aug 2016 22:49:42 GMT

I guess the issue here is that set analysis is defined once per chart and cannot be done for each row. Like vishsaggi‌ mentioned, we might be able to help you better if you are able to share a sample with us.

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample