Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Subtotal with only visible values

Hey everyone, good morning!

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

Ex: (Production of a 'x product')

DayMorning (lb)Afternoon (lb)Desv.
18/051000500-500
19/05200300100
20/0540050004600

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:

DayMorning (lb)Afternoon (lb)Desv.
18/051000500-500
20/540050004600

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

DayMorning (lb)Afternoon (lb)Desv.
18/051000500-500
20/0540050004600
Total4200

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

Kind regards, Luiz Bisco

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

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))

View solution in original post

7 Replies
stabben23
Partner - Master
Partner - Master

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

richnorris
Creator II
Creator II

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),

          0)

         )

marcus_sommer

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

sum(aggr(YourExpression, Day))

- Marcus

tamilarasu
Champion
Champion

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))

Not applicable
Author

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

Not applicable
Author

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:

DayMorning (lb)Afternoon (lb)Desv.Desv %
18/05

1000

500-500-50%
20/05400500046001150%
Total1400550041001100%

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

tamilarasu
Champion
Champion

Hi Luiz,

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


[Desv.] / [Morning (lb)]


Untitled.png