Sub total in pivot table

[Anonymous]

Hi

In the attached application ‘sales’ is  given a background color by comparing it with Goal1. But the Total which is displayed at the last must be compared against Goal 2 and given color. Qlikview generates the Total (By enabling the partial sum option). So I am not able to change the condition for background color of Total Column.

I tried by removing the partial sum and created a calculated dimension. It didn't work.

Could anyone help on this.

Thanks!

[sunny_talwar]

This is what you are looking for?

Used this expression for Backgroun Color:

=If(Dimensionality() = 1,

If((sum(Numerator)/Sum(Denominator))*1000>avg(Target2),RGB(185,0,0),RGB(69,234,21)),

If((sum(Numerator)/Sum(Denominator))*1000>avg(Target1),RGB(185,0,0),RGB(69,234,21)))

HTH

Best,

Sunny

[Anonymous]

Thanks for the answer.

Could you please help me in getting vertical column(Total) getting compared against Goal2 and display the background color.

[sunny_talwar]

Try this:

=If(Dimensionality() = 3 and SecondaryDimensionality() = 0,

If((sum(Numerator)/Sum(Denominator))*1000>avg(Target1),RGB(185,0,0),RGB(69,234,21)),

If((sum(Numerator)/Sum(Denominator))*1000>avg(Targer2),RGB(185,0,0),RGB(69,234,21)))

The above expression will evaluate the table portion against Target1 and all the totals against (including the final total) against Target2

HTH

Best,

Sunny

[Anonymous]

Thanks once again for the help.

But I have one more concern.

The Horizontal column(Total)must be compared against Target1 and Vertical Column(Total)must be compared against Target2.

Could you please help me on this.

Thanks!

[sunny_talwar]

Can you list out what gets compared to what? There are three subtotals if you can explain... will help you what you wanted.

Best,

Sunny

[Anonymous]

The Sales is compared against Target1 and the background color is decided(Between Green and Red)

The partial sum which is displayed vertically must be compared against Target2.

The partial sum which is displayed at the bottom(Region level partial sum)must be compared against Target1.

[sunny_talwar · Accepted solution]

Try this:

=If(ColumnNo() = 0 and (Dimensionality() = 0 or Dimensionality() = 1 or Dimensionality() = 2),

If((sum(Numerator)/Sum(Denominator))*1000>avg(Targer2),RGB(185,0,0),RGB(69,234,21)),

If((sum(Numerator)/Sum(Denominator))*1000>avg(Target1),RGB(185,0,0),RGB(69,234,21)))

Not sure if you need *1000 or not because you are comparing a time 1000 to a small number. The output will always be in red based on the above condition. You might want to use this instead:

=If(ColumnNo() = 0 and (Dimensionality() = 0 or Dimensionality() = 1 or Dimensionality() = 2),

If((sum(Numerator)/Sum(Denominator))>avg(Targer2),RGB(185,0,0),RGB(69,234,21)),

If((sum(Numerator)/Sum(Denominator))>avg(Target1),RGB(185,0,0),RGB(69,234,21)))

In the above image A is getting compared to Target 2 and B is getting compared to Target 1.

HTH

Best,

Sunny

[Anonymous]

Can you tell against what the row highlighted in blue compared with.

[sunny_talwar]

Those are against Target1