topic Re: Use different dimension when calculating an expression in QlikView

Use different dimension when calculating an expression

Tue, 09 Jun 2015 23:17:58 GMT

I have a straight table with a single dimension "Client.Subproduct" and several expressions "sum(Revenue)", "count(customers)", etc. ... I want to add a new expression which ignores my dimension ("Client.Subproduct") as well as any selections, and instead gives me the sum of Revenue for Client.Product where Industry equals A. I tried the following:

=Aggr(sum({1<Industry={'A'}>} Total <Client.Product> Revenue), Client.Product,Client.SubProduct)

If you imagine a raw data table which looks like this:

Client.SubProduct Client.Product Industry Revenue

A F A 2

B F A 4

C F A 6

D F A 10

E F B 20

In my final table, the expression looks correct for rows where dimension = A, B, C, D ... but for rows where dimension = E, the expression appears as null ... I think this happens because there is no record in the data where SubProduct = E, Product = F, and Industry = A ... however, the requirement is to sum Revenue for Client.Product where Industry equals A (i.e., I don't care about Subproduct for this expression)

Hope that's not too confusing, not sure how unique this case is ... really appreciate any help here. Thanks!

Re: Use different dimension when calculating an expression

oknotsen — Wed, 10 Jun 2015 13:17:52 GMT

Try ALL instead of {1}.

{1} still follows the dimensions you use in your table. ALL simply takes ALL data and even ignores dimensions in a chart.

Re: Use different dimension when calculating an expression

Wed, 10 Jun 2015 14:00:28 GMT

wow ... can't believe it's that simple. Thanks so much.

Re: Use different dimension when calculating an expression

swuehl — Fri, 26 Jun 2015 18:01:08 GMT

Just be aware that ALL is a qualifier that may not be supported in future versions (well, it's been there for ages):

In previous QlikView versions, the all qualifier may occur before an expression. This is equivalent to using {1} total. In such a case the calculation will be made over all the values of the field in the document, disregarding the chart dimensions and current selections. (The same value is always returned regardless of the logical state in the document.) If the all qualifier is used, a set expression cannot be used, since the all qualifier defines a set by itself. For legacy reasons, the all qualifier will still work in this QlikView version, but may be removed in coming versions.

{1} TOTAL is the equivalent to ALL