Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
googel84
Partner - Creator III
Partner - Creator III

Set analysis less than condition on a field

Hi everyone, I just want to be sure I correctly understood set analysis principals. I checked some other posts without finding the exact answer.

Is it possible to build a set analysis statement using a field to define the filter condition? I.e.

sum({<[Register data] = {'<= $([Reference date])'}>}Amount)

I'm not confident the answer is affirmative. In fact, [Reference date] could have multiple values and, since what is put between braces and apostrophes is what you should put in search popups, this makes the entire formula to evaluate tu null.

Is it right? Or is there a way to reach the desired result?

Thanks

P.S.1 : [Register date] and [Reference Date] are fields of the same record.

P.S. 2: using aggregation function (such as max(), min(), ...) it should work, but it's not what we want.

1 Solution

Accepted Solutions
gandalfgray
Specialist II
Specialist II


googel84 wrote:
Hi Goran, thank you for contributing. The meaning of my P.S.2 was that I cannot use aggregation function such as max().
Thanks<div></div>


Well I understood that, but what I did not understand was _why_ you cannot use max() the same way I used it in my example.

As you noticed yourself, you can't use a field representing more than one value in a set expression without modifying it with max() or min() or another function so you get just one value.

If you can't do that I don't know how to solve your case.

View solution in original post

7 Replies
Not applicable

When using variables, you should use it as $(=variableName)

In your case:

sum({<[Register data] = {'<= $(=[Reference date])'}>}Amount)

googel84
Partner - Creator III
Partner - Creator III
Author

Thank you Miha for your quick reply, but your suggestion is not correct. I can provide you with a plenty of examples of set analysis made through variables and the syntax can be also the one I used.

Anyway, the point is that [Register date] is a field and not a variable.

gandalfgray
Specialist II
Specialist II

This syntax works for me:

count({$<BusinessStartDate={"<=$(=Date(Max(PDate)))"} >} Distinct PID)

(and PDate is a field not a variable)

In my case, since PDate can have multiple values, I have to use Max() function, so I get only one value after the "<="

I am not sure what you mean by your PS2

googel84
Partner - Creator III
Partner - Creator III
Author

Hi Goran, thank you for contributing. The meaning of my P.S.2 was that I cannot use aggregation function such as max().

Thanks

gandalfgray
Specialist II
Specialist II


googel84 wrote:
Hi Goran, thank you for contributing. The meaning of my P.S.2 was that I cannot use aggregation function such as max().
Thanks<div></div>


Well I understood that, but what I did not understand was _why_ you cannot use max() the same way I used it in my example.

As you noticed yourself, you can't use a field representing more than one value in a set expression without modifying it with max() or min() or another function so you get just one value.

If you can't do that I don't know how to solve your case.

Not applicable

GandalfGray, the question is how to specify if you want to sum the amount where Register Date is less than or equal to Reference Date on the same record, not where it is less than or equal to ALL Reference Dates, or the minimum or maximum of all. How would you do that?

Not applicable

GandalfGray, the question is how to specify if you want to sum the amount where Register Date is less than or equal to Reference Date on the same record, not where it is less than or equal to ALL Reference Dates, or the minimum or maximum of all. How would you do that?