Using set analysis to filter on the date in the dimension

[#1]

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.

[sunny_talwar]

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

[vishsaggi]

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

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

[#4]

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

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.

[vishsaggi]

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

[#6]

Try this expression:

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

[#7]

I did try this expression and it did not work:

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

[sunny_talwar]

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