cancel
Showing results for
Did you mean:
Not applicable

## Set Analysis with <= Operator

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.

1 Solution

Accepted Solutions
Specialist II

Try this:

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

8 Replies
Employee

Hello Andrew,

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

Hope that helps

Specialist II

Try this:

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

Partner - Champion III

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
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

Employee

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

Champion III

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)

Community Browser