Pivot chart filtering dilemma

[joshcrd]

Hi All,


First poster here. My names Josh and I'm a business analyst in Sydney, Australia. Firstly I have learned so much from the free tutorials and Chandoo is great at explaining things logically and clearly.


I have searched the forums extensively (not just this one) but cant find a clear solution.


Basically I have a pivot table/chart that I want to filter for a team of sales representatives.


I want the top report filter to represent "State", with a secondary filter below to select "territory". This is possible with pivot charts although when I select a State in the first drop down box, I want the second one to only show the territories in that state. Instead it shows all the territories in entire country (although if you try to select one that isnt in that state no data is displayed). Still I was wondering if the secondary report filter could be limited by the criteria selected in the primary report filter.


Hope this makes sense


Josh

 

[Faseeh]

Hi joshcrd,


Welcome to the chandoo's forums!


Consider uploading a (sample) file. This perhaps will speed-up helping process for your problem. Upload it to any file sharing site and drop its link here so that any one can access it.


Happy Posting!


Regards,

Faseeh

 

[joshcrd]

Thanks Faseeh


I dont think I can do that as the data is confidential


how about a screenshot?


do you know anywhere I can post a temporary picture of the screenshot, then I can describe it to you

 

[Hui]

Josh


Firstly, Welcome to the Chandoo.org Forums


Please read: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[joshcrd]

Okay thanks Hui, the pic below shows a snippet of how my original data is laid out, my pivot table needs to have "Territory" as the primary report filter, and "SRA" as the secondary report filter (fyi SRA stands for Sales Reporting Area, and is a geographical level below Territory)


http://oi48.tinypic.com/2qxqbnp.jpg


The pivot table works as intended although when I select a Territory in the primary filter, I still get all the SRA's in the secondary filter. In this example below I have selected NSW North as the primary report filter although I can still select SRA's from other Territory's (e.g. NSW South and QLD which I highlighted)


http://i48.tinypic.com/2vilg2h.jpg


Is there a way I can make the secondary field (SRA) depend on the first field (Territory) so that it only displays the SRA's that correspond to the territory selected?


Below is a pic of how my table is laid out in the field view


http://i48.tinypic.com/a1oebm.jpg

 

[Faseeh]

Hi joshcrd,


If you remove "Territory" & "SRA" from "Report Filters" and place them in the same sequence in "Row Labels" followed by "Values" then only those SRA's will be displayed that are under Certain Territory. This must be the settings:


Col Labels:

Start Date


Values:

Sum of Dollars

Sum of Units


Row Labels:

Teratory

SRA

Sum of Values


Hope this helps.


Regards,

Faseeh

 

[joshcrd]

thanks Faseeh, I should give you guys some more background


is there any way to get it to behave like a report filter?


I want to have a dynamic chart linked to this table with two drop down boxes, one for territory, the second for sra


if both boxes are blank it will just show the total


if the first box is selected and the second box is blank then it will show a total for that territory


if they make a selection in the second box the only available selections will be the ones that correspond to their territory


is there any way to achieve this? I can do it through lookups and validation lists but I would prefer to use pivot tables because I will be updating with monthly data