topic Re: Subtotal with only visible values in QlikView

Subtotal with only visible values

Fri, 20 May 2016 13:07:08 GMT

Hey everyone, good morning!

I have a pivot table that calculates volume deviations between two periods.

Ex: (Production of a 'x product')

Day	Morning (lb)	Afternoon (lb)	Desv.
18/05	1000	500	-500
19/05	200	300	100
20/05	400	5000	4600

Where 'Day' is a dimension and Morning, Afternoon and desv are expressions.

All these expressions are conditionated to this condition: If(sum of morning) - sum(afternoon) is > 400 or <-400, expression (sum or m/a), else, 0.

After that I hide 0, so my table gets this way:

Day	Morning (lb)	Afternoon (lb)	Desv.
18/05	1000	500	-500
20/5	400	5000	4600

However, when I insert the subtotal into the table, it remains calculating the whole table:

Day	Morning (lb)	Afternoon (lb)	Desv.
18/05	1000	500	-500
20/05	400	5000	4600
Total			4200

Do you guys know how to sum only the visibles values?

Kind regards, Luiz Bisco

Re: Subtotal with only visible values

stabben23 — Fri, 20 May 2016 13:43:35 GMT

Add a sum first in Your Expression. Do not start With an if statment:

Re: Subtotal with only visible values

richnorris — Fri, 20 May 2016 13:50:47 GMT

You could change your Desv calculation, to be the one you said,

If(sum of morning) - sum(afternoon) is > 400 or <-400, expression (sum or m/a), else, 0

so that your top chart (prior to conditional show) would have a 0 in the Desv column. This way your subtotal would be what you expect?

/Edit: Also, yes as Staffan says, its not good practice to start with the if, you should start with 'SUM' in the epression, so something like:

SUM( if( (sumOfMorning)-(sumOfAfternoon) >400

or (sumOfMorning)-(sumOfAfternoon) <-400,

(sumOfMorning - sumOfAfternoon),

)

Re: Subtotal with only visible values

marcus_sommer — Fri, 20 May 2016 13:53:13 GMT

You need to calculate this on the day-level with an aggr-function, like:

sum(aggr(YourExpression, Day))

- Marcus

Re: Subtotal with only visible values

tamilarasu — Fri, 20 May 2016 14:09:33 GMT

Hi Luiz,

Yes. You need to use Aggr in order to get total value of visible rows. Like below

Sum(Aggr(If(sum ([Morning (lb)]) - sum([Afternoon (lb)])>400 or sum ([Morning (lb)]) - sum([Afternoon (lb)])< -400,Sum([Morning (lb)]),0),Day))

Re: Subtotal with only visible values

Fri, 20 May 2016 14:19:58 GMT

But if I do that, how can show only the desviations that I want (only > 400 or <-400)?

Re: Subtotal with only visible values

Fri, 20 May 2016 16:49:23 GMT

Tamil, thank you!

It works, but now I have another problem. The total is correct for the summing, but i tried to insert a column with percent (and the conditions to calc remains). see the example:

Day	Morning (lb)	Afternoon (lb)	Desv.	Desv %
18/05	1000	500	-500	-50%
20/05	400	5000	4600	1150%
Total	1400	5500	4100	1100%

The desv %, should be 292,8% (4100/1400), but, its is giving me the sum of Desv %.

Do you know how to handle with it?

I'm already greatful,

Sincerely, Luiz Bisco

Re: Subtotal with only visible values

tamilarasu — Fri, 20 May 2016 17:23:27 GMT

Hi Luiz,

I have attached a sample app. You can simply refer the column label like below,

[Desv.] / [Morning (lb)]