topic Re: Controlling Subtotals in Pivot Table when dimensions are switched. in QlikView

Controlling Subtotals in Pivot Table when dimensions are switched.

retko1985 — Wed, 18 Oct 2017 09:32:12 GMT

Hello,

please refer to the attached QVW.

I have Country as second dimension, because of Rank Sort. Now when I click Vencouver, I see subtotal for vencouver, but I want to see still for whole country (Canada).

Is it possible?

Thank you.

Re: Controlling Subtotals in Pivot Table when dimensions are switched.

Anil_Babu_Samineni — Wed, 18 Oct 2017 10:34:57 GMT

Like this?

Re: Controlling Subtotals in Pivot Table when dimensions are switched.

retko1985 — Wed, 18 Oct 2017 11:18:23 GMT

Not exactly..

From my data the Country total for Canada is 60.

Basically what I want is when I click some city from Canada, it will display all cities from Canada in the graph, this is done by P in set analysis. Now I want to display total for Country under the selected City. (and also later I want to hide other subtotals, but this is probably for other question I guess).

Thanks

Re: Controlling Subtotals in Pivot Table when dimensions are switched.

sunny_talwar — Wed, 18 Oct 2017 11:27:40 GMT

This?

Re: Controlling Subtotals in Pivot Table when dimensions are switched.

retko1985 — Wed, 18 Oct 2017 12:58:26 GMT

Hi, this is great.

Just one little thing, do you know how can I hide the country column and still make it work? Also similar, when I deselect all cities, there should not be country totals at each row, but this should be achieved when hiding country.

Also I just notice that total for all cities when no cities are selected is incorrect:

Thank you.

Re: Controlling Subtotals in Pivot Table when dimensions are switched.

sunny_talwar — Wed, 18 Oct 2017 13:01:28 GMT

I think it would be helpful to see an Excel file with how you want to see the result with and without selections

Re: Controlling Subtotals in Pivot Table when dimensions are switched.

sunny_talwar — Wed, 18 Oct 2017 13:25:28 GMT

Check the attached and see if this works... I have used a one time macro to hide Country dimension....

Re: Controlling Subtotals in Pivot Table when dimensions are switched.

retko1985 — Wed, 18 Oct 2017 13:53:21 GMT

I got a little confused with last message, so I had to delete it, but I see you already saw it.

Basically What i need is a little different (But I still can use these techniques).

Without selection it would be a basic view like this:

With Selection it would highlight the selected city, and below it the total for the county will appear, something like this:

Do you thing it is possible?

Thank you.

Re: Controlling Subtotals in Pivot Table when dimensions are switched.

retko1985 — Wed, 18 Oct 2017 13:55:53 GMT

I still see the 0 for cities which are not selected:

But look at my response above.

Thanks

Re: Controlling Subtotals in Pivot Table when dimensions are switched.

sunny_talwar — Wed, 18 Oct 2017 14:06:51 GMT

You keep changing your requirement... check now

Re: Controlling Subtotals in Pivot Table when dimensions are switched.

retko1985 — Wed, 18 Oct 2017 16:48:13 GMT

Exactly what I wanted. Thank you.