topic Re: Use different dimensions for single expression in QlikView

Use different dimensions for single expression

Wed, 04 Mar 2015 15:22:47 GMT

I have been unable to find a specific question like this on the forums, though I may just be searching in the wrong way since it doesn't seem a particularly uncommon case. Another way to phrase it might be 'how to use a dimension (2 below) for only a single expression'.

I would like to have a pivot table where one expression uses fewer dimensions than the other (see below). Set analysis or dimensionality with partial sums leave duplicated or blank rows in the table respectively, so that doesn't give me what I want. Any help is greatly appreciated.

What I want:

The closest I can get to that is to use dimensionality() and partial sums in the pivot table, which gives this:

All the values / totals are correct here, but I have entire rows of pointless blank cells. Tried unsuccessfully to use conditional on 'Expression 1', but then it either appears or not in the entire table, rather than row-by-row.

Re: Use different dimensions for single expression

JonnyPoole — Wed, 04 Mar 2015 15:35:21 GMT

How about this ?

the 1st expression has a formula of: if( dimensionality()=1, Count(distinct Customer), Sum(Sales))

Re: Use different dimensions for single expression

Wed, 04 Mar 2015 16:06:20 GMT

Jackpot. Can't believe using a single if/else expression escaped me, but this is exactly what I needed. Thanks!

Re: Use different dimensions for single expression

sunny_talwar — Wed, 04 Mar 2015 16:20:04 GMT

I was never able to understand the concept of dimensionality before today. Thanks to your sample qvw file, I have a much better idea about it and will def. use this in the future.

Best,