topic Re: Sub total in pivot table in QlikView

Sub total in pivot table

Anonymous — Mon, 15 Jun 2015 12:10:32 GMT

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!

Re: Sub total in pivot table

sunny_talwar — Mon, 15 Jun 2015 12:30:09 GMT

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

Re: Sub total in pivot table

Anonymous — Mon, 15 Jun 2015 12:50:38 GMT

Thanks for the answer.

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

Re: Sub total in pivot table

sunny_talwar — Mon, 15 Jun 2015 12:59:39 GMT

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

Re: Sub total in pivot table

Anonymous — Mon, 15 Jun 2015 13:28:43 GMT

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!

Re: Sub total in pivot table

sunny_talwar — Mon, 15 Jun 2015 13:33:33 GMT

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

Re: Sub total in pivot table

Anonymous — Mon, 15 Jun 2015 13:44:23 GMT

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.

Re: Sub total in pivot table

sunny_talwar — Mon, 15 Jun 2015 14:47:11 GMT

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

Re: Sub total in pivot table

Anonymous — Mon, 15 Jun 2015 14:51:38 GMT

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

Re: Sub total in pivot table

sunny_talwar — Mon, 15 Jun 2015 14:54:12 GMT

Those are against Target1

Re: Sub total in pivot table

Anonymous — Tue, 16 Jun 2015 05:10:37 GMT

I am getting the background color but i have one more problem after implementing the above mentioned change.

i am getting a row like,

which was not there before the change.

Could you please help me on this.

Re: Sub total in pivot table

Anonymous — Tue, 16 Jun 2015 09:39:23 GMT

But when i remove the expression mentioned above i am nt getting any black row(like the one shown in the previous mesaage image)

Re: Sub total in pivot table

sunny_talwar — Tue, 16 Jun 2015 10:11:40 GMT

So the qvw you posted up top was an example or was it the actual qvw you are working with? If it was an example, does the pivot table structure between your actual table and example table differs? Extra Dimensions? Extra expressions, anything is different like that????

Re: Sub total in pivot table

Anonymous — Tue, 16 Jun 2015 10:17:21 GMT

Ya the one i posted is sample.

There is no difference between the two. Only the names of the dimensions and values vary.

Re: Sub total in pivot table

sunny_talwar — Tue, 16 Jun 2015 10:20:23 GMT

Not sure, attaching the version you sent, may be you can figure what is different between the two versions. (PFA)

Best,

Sunny

Re: Sub total in pivot table

Anonymous — Tue, 16 Jun 2015 10:26:26 GMT

ya sure i can.

Really thanks a lot for you help.

can you explain me the concept of dimensionality and column no with an example plz.

i was nt able to understand clearly from what i searched in net.

Re: Sub total in pivot table

sunny_talwar — Tue, 16 Jun 2015 10:35:31 GMT

I can try to explain you, but I think there are good resources on community and it would be better to guide you towards those resources rather than trying to explain them again.

https://www.qlikcommunity.com/docs/DOC-8006

How to use - Dimensionality()

I am sure you will understand everything that you need to know about Dimensionality() and SecondaryDimensionality() as Robert_Mika has done a great job of explaining them with examples. If you still don't understand, I can def. try to help you with another example.

HTH

Best,

Sunny

Re: Sub total in pivot table

Anonymous — Thu, 18 Jun 2015 08:48:29 GMT

Thanks a lot

Re: Sub total in pivot table

Anonymous — Thu, 18 Jun 2015 14:16:20 GMT

The total row highlighted displays the Continent's total. But when i select a city say Delhi ,the total row is displaying Delhi's total alone. I expect it to display Asia's total even when a city is selected. Is there a way to implement that?

Could you please help me on that.

Also if a continent is selected i expect the above highlighted row to show the total of all the three continents.

Thanks!

Re: Sub total in pivot table

Anonymous — Thu, 18 Jun 2015 14:47:52 GMT

Could you please help me on that.

Also if a continent is selected i expect the above highlighted row to show the total of all the three continents.

Thanks!