I'm new to using alternate states and I haven't seen anything that's helping me out. I have a single data set that I need to be able to view side by side against itself (perfect use for alternate states). What I need to be able to do though is have a "main level" filter that works on both states at the same time, then use filters built on the state level to further drill down the data.
I've created State1 and State2 running the same pivot charts (based off their own State), then I build a quick List box using the "Location" field from the dataset and used <default state> with no luck. This Location filter needs to be like the top level filter so that both States will filter down to say NorthEast, then I want to make filters on the state to dig deeper, which should be as easy as just building the filter with the right State1 or State2 in it. The "Location field" lives on both states pivot table too.
From what I've read on alternate states its always formulas about how to do set analysis etc across both sets, which might be the way to go, but I'm missing something cause its just not hitting home with me on how this is done.
You explained it as trying to sum the data etc, but what I want to do is the drop down to do is be the 1st drill down on BOTH states at once..
So if I click Location do be "NE", both AS1 crosstab and AS2 crosstab are now only showing "NE" data. The reason i need the 2 states is so i can then drill down side-by-side the 2 pivots with more filters on each state itself.
I can always just build that top level filter on the state level too I suppose, but it shouldn't be difficult.
Does that make sense?
Do i need to pass location to a variable and then somehow incorporate the variable in to the location inside each state?