Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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

Re: Subtotal with only visible values

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
Honored Contributor

Re: Subtotal with only visible values

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

Highlighted
richnorris
Contributor II

Re: Subtotal with only visible values

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)

         )

MVP & Luminary
MVP & Luminary

Re: Subtotal with only visible values

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

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

Not applicable

Re: Subtotal with only visible values

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

Not applicable

Re: Subtotal with only visible values

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

Re: Subtotal with only visible values

Hi Luiz,

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


[Desv.] / [Morning (lb)]


Untitled.png