Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

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
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)

Partner
Partner

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.

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

Partner
Partner

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

Thanks

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

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?