Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
gandalfgray
Valued Contributor

SV:Set Analysis with <= Operator

Try this:


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


8 Replies
MVP
MVP

Set Analysis with <= Operator

Hello Andrew,

Use instead

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


Hope that helps

gandalfgray
Valued Contributor

SV:Set Analysis with <= Operator

Try this:


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


MVP
MVP

Set Analysis with <= Operator

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

SV:Set Analysis with <= Operator

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

Not applicable

Set Analysis with <= Operator

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

Not applicable

Set Analysis with <= Operator

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
Employee

Set Analysis with <= Operator

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

MVP
MVP

Set Analysis with <= Operator

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