topic Set Analysis with <= Operator in QlikView

Set Analysis with <= Operator

Thu, 05 Aug 2010 12:25:50 GMT

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.

Set Analysis with <= Operator

Miguel_Angel_Baeyens — Thu, 05 Aug 2010 12:59:34 GMT

Hello Andrew,

Use instead

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

Hope that helps

SV:Set Analysis with <= Operator

gandalfgray — Thu, 05 Aug 2010 13:04:49 GMT

Try this:


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

Set Analysis with <= Operator

jonathandienst — Thu, 05 Aug 2010 13:09:34 GMT

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

SV:Set Analysis with <= Operator

Thu, 05 Aug 2010 13:37:47 GMT

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

Set Analysis with <= Operator

Thu, 05 Aug 2010 13:38:34 GMT

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

Set Analysis with <= Operator

Thu, 05 Aug 2010 13:39:42 GMT

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.

Set Analysis with <= Operator

Clever_Anjos — Tue, 14 Sep 2010 21:01:52 GMT

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

Set Analysis with <= Operator

johnw — Wed, 15 Sep 2010 00:49:26 GMT

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)