Set analysis less than condition on a field

[googel84]

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.

[#2]

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

In your case:

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

[googel84]

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]

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]

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 · Accepted solution]

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.

[#7]

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?

[#8]

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?