topic Re: Referring to Calculated Dimension in QlikView

Referring to Calculated Dimension

Mon, 08 Jul 2013 09:23:24 GMT

First post

I need help to improve an expression of mine...

I have at pivot table with one horizontal and one veritical dimension, both calculated (class intervals). Are there any way to reference the column/column value in expressions e.g. in an IF or TOTAL modifier statement?

The wanted result should be something like SUM(NUMBER)/SUM (TOTAL <"Calculated Dimesion"> NUMBER) or an equivalent SUM(IF).

Re: Referring to Calculated Dimension

Gysbert_Wassenaar — Mon, 08 Jul 2013 09:40:11 GMT

You can't use calculated dimensions as field name when using the total modifier. You can only use real field names. In an if statement you can use the expression of the calculated dimension. You might want to put that expression in a variable in that case to make reuse easier.

Re: Referring to Calculated Dimension

Mon, 08 Jul 2013 09:58:48 GMT

Hi and thanks for quick reply.

Problem is the calculated dimension are a class interval so the expression result would be 500 <= x < 550 which are not direct usable in an IF statement.

It also gets compared to all values in the SUM statement meaning that null or missing values in the data makes the expression returns a NULL.

Re: Referring to Calculated Dimension

Fri, 20 Sep 2013 04:08:20 GMT

Hello 🙂

I've been struggling with calculating the share % on a calculated dimension for a while myself, and just came up with this. It seems to work so far in my pivot table:

sum(NUMBER)/ bottom(rangesum(above(sum(NUMBER),0,NoOfRows())))

Hope this works for you!

Karen

Re: Referring to Calculated Dimension

Fri, 20 Sep 2013 09:51:50 GMT

Hi Karen

Thanks for replying in an old discussion...

It did not work entirely correct but you got me on the right track. You divide by the sum of rows i was looking for columns.

fixed it with following:

=SUM(NUMBER)/RangeSUM(First(TOTAL SUM(NUMBER),1,NoOfColumns()))

Re: Referring to Calculated Dimension

Fri, 20 Sep 2013 13:30:15 GMT

Cool! ☺