Parent level and Child level metrics

[#1]

1. I have Region and Sub region level metrics. When I expand to sub region, the region level metrics gets lost. Is there a way we can retain the region level and sub region level numbers. Ideally, when a user selects NA, I want to show, NA, US and CA columns.
2. Not able to get the months sorted. When I select a particular month, the order changes. Is there a way we can correct it?

Thanks for your time.

Regards,

Tina

[arniewolff]

Tina,

1. I'm not sure I follow this question. When I expand the pivot table to show the sub regions in columns, they remain when I select NA as shown in the enclosed file. Can you elaborate?

2. If you sort the months list box by numeric value, the months show in the correct order and dont move around as shown in the file.

Arnie

[#3]

Arnie,

Thanks for your response. Apologize for not being clear. Let me try to explain..

1. When the list is not expanded, a value for NA is being shown. But when we expand the list that value breaks in to CA and US. What I would like to have is three metric columns: NA, US, CA. I do not want to lose the regional level metrics when drilling down, the user wants to see both region and sub region level metrics side by side.

I tried some thing else, in another sheet. Tired to clone the object at the sub region level. This works for me but in this method, when i filter on region, GLOBAL(partial sum) is also getting affected. Ideally, i do not want global value to be affected when we filter on region or sub region. Is that doable. I know this is a crude way to do it and I would still like to go be a cleaner approach by having just one table as mentioned above. But being new to qv, i am just trying. can you please help me on any one of the options.

2. For sorting month, I made the necessary changes, but I think I am missing some thing. Its not working for me. The months are not getting sorting.

Can you please take a look when you have some time.

Thanks again for your time.

Regards,

Tina.

[arniewolff]

Tina,

I have 2 suggestions to try.

1. On the Presentation tab of the pivot table, select Show Partial Sums for CV_SUB_REGION. That should leave the total for the columns after expanding to show US and CA.

2. In your script, change this

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

to this

SET MonthNames='JAN;FEB;MAR;APR;MAY;JUN;JUL;AUG;SEP;OCT;NOV;DEC';

and then sort the Month list box by State (Auto Ascending) and Numeric Value (Ascending).

Let me know if that changes the order. As I play with this, I'm getting some pretty strange results!

Arnie

[#5]

Thanks Arnie. Enabling the sub totals for sub region helped. I am trying to get the region name itself on the total label but unable to get it.

Also, i am also having weird order for months after putting all the conditions..

Can you please take a look..

[arniewolff · Accepted solution]

Tina,

I was able to get the months to work correctly from the file you sent by clearing the selection for the month and the year - that puts the months in the correct order with no selections. Then I adjusted the sort properties and unselected State and Expression. For me, that left the months in the correct order and doesn't allow them to move.

Let me know what you get when you try that.

I researched the region name topic some more. This prior discussion regarding labels in pivot tables makes it sound like it's an issue that is not yet resolved. I couldnt' get it to work how you want.

Label for Totals Expressions in Pivot Table

Arnie

[#7]

Thanks Arnie for your time. My months are sorted now.

Thanks again for researching on the Dynamic Pivot Table Total Headings. I will look around if there is any solution at all..

Regards,

Tina.