topic How do I overide a chart dimension within the same chart expression? in QlikView

How do I overide a chart dimension within the same chart expression?

bobnewbie — Wed, 25 Nov 2020 16:16:04 GMT

Hi All, please can you assist:

I have a chart with 2 dimensions, Product and Year and a single expression sum(Sales). I would like to create another expression that shows the percentage:

total sales of product (but only for that dimension year) / total sales of all products (but only for that dimension year as well)

Example:

Product Year Sales %

Apple 2015 5 5 / (5+7+9)

Apple 2016 6 6 / (6+8)

Pear 2015 7

Pear 2016 8

Banana 2015 9

Thanks all.

Re: How do I overide a chart dimension within the same chart expression?

arulsettu — Thu, 04 Aug 2016 13:50:06 GMT

sum(sales)/sum(TOTAL sales)

Re: How do I overide a chart dimension within the same chart expression?

kumarkp412 — Thu, 04 Aug 2016 13:52:37 GMT

Hi Bob,

For secound expression use the aggr function with total of sales by year.

try this;

ex: sum(sales)/aggr(sum(sales),year)

Re: How do I overide a chart dimension within the same chart expression?

settu_periasamy — Thu, 04 Aug 2016 14:05:01 GMT

May be try

=sum (Sales)/sum (TOTAL <Year> Sales)

Re: How do I overide a chart dimension within the same chart expression?

jonathandienst — Thu, 04 Aug 2016 14:10:56 GMT

settu_periasamy has the correct answer for general use:

=sum (Sales)/sum (TOTAL <Year> Sales)

You should avoid using Aggr() for expressions like this (aggr() can be a performance killer and should be used sparingly).

Use set analysis if you want to limit to the current year only

Re: How do I overide a chart dimension within the same chart expression?

bobnewbie — Fri, 05 Aug 2016 06:23:16 GMT

Many thanks all. Thanks Jonathan, the team and I have learned something new and very useful.