Set Analysis with <= Operator

[#1]

I'd like to be able to use the less than or equal to operator (<=) in a Set Analysis expression, but it doesn't appear to work.

The expression I have written is:

=SUM({$<FinancialYear={$(=max(FinancialYear))}, PeriodNo<={$(PeriodSelection)}>} [ExtendedInvoicePrice])

It seems as though the < symbol in <= is being related to the required < at the beginning of the statement (after the first $).

Does anyone have any ideas on what I need to do to get this to work correctly?

For info, this expression is supposed to return the total value of sales invoices in the current year (FinancialYear) up to and including the selected period (PeriodSelection).

Any help would be much appreciated.

[Miguel_Angel_Baeyens]

Hello Andrew,

Use instead

=SUM({$<FinancialYear={$(=max(FinancialYear))}, PeriodNo={<=$(PeriodSelection)}>} [ExtendedInvoicePrice])

Hope that helps

[gandalfgray · Accepted solution]

Try this:

=SUM({$<FinancialYear={$(=max(FinancialYear))}, PeriodNo={"<=$(=PeriodSelection)"}>} [ExtendedInvoicePrice])

[jonathandienst]

Is PeriodSelection a field, or a variable?

If a field, then you shouldn't use the $ expansion. Like this:

<pre>SUM({$<FinancialYear={$(=max(FinancialYear))}, PeriodNo={"<=PeriodSelection"}>} [ExtendedInvoicePrice])

If its a variable, then I think Goran is correct.

Jonathan

[#5]

Goran - your expression has worked! Perfect! Thank you!

[#6]

Jonathan - PeriodSelection is a variable and as you said Goran's suggestion is correct. Thank you

[#7]

Miguel - your suggested expression did not work at all I'm afraid, it returned null values for all rows. Goran's suggested expression worked though.

[Clever_Anjos]

Hello Jonathan,

I was reading your post and I need to filter an dimension based on another dimension, just like you sugested.

In my case

Max

({<DataRegistro={'<=MesRef'}>} DataRegistro)

Where DataRegistro is a dimension on a table and MesRef is a Dimension on another table and is the main dimension on a Chart

My code does not work, always returnig null,

Any ideas?

Best regards

[johnw]

Three possibilities that I can think of. First, I recommend putting literals in single quotes, and search expressions in double quotes. QlikView allows some overlap, but getting it wrong can cause problems sometimes. Second, if MesRef can have multiple values, then it will evaluate to null, so the expression won't work. In that case, you might need something like max(MesRef) instead. Third, unlike in normal expressions, when used in set analysis like this, dates must be in the same format to be compared. So if DataRegistro has values like "Aug 30, 2010" and MesRef has values like "30/8/2010", those are not comparable in set analysis. You would need to change the format of MesRef to match the set. Oh, and when making an expression inside of set analysis, you generally need to do it with dollar sign expansion.

So this probably won't work for YOUR data, but just to match up with the example I've given:

max({<DataRegistro={"<=$(=date(max(MesRef),'MMM DD, YYYY'))"}>} DataRegistro)