Help with Pivots/Dimensions

[#1]

Hi,

I am new to qv & am trying to create a smart pivot.

I have a table with prices of products across stores & states for a given date range. I need to create a pivot table that would highlight products that have different prices across stores in a given state for a given date. Ideally, I need a pivot table that will give me Date, State, #ProductsWithMultiplePrices. On clicking the specific Date/State, it should expand into the list of products with multiple prices.

Consider the below dataset :

ProductPrices:

LOAD * INLINE

[
Date, State, Store, Product, Price
1-Jan-2013, S1, ST11, P1, 101
1-Jan-2013, S1, ST11, P2, 102
1-Jan-2013, S1, ST12, P1, 102
1-Jan-2013, S1, ST12, P2, 102
1-Jan-2013, S2, ST21, P1, 110
1-Jan-2013, S2, ST21, P2, 120
1-Jan-2013, S2, ST21, P3, 130
1-Jan-2013, S2, ST22, P1, 111
1-Jan-2013, S2, ST22, P2, 120
1-Jan-2013, S2, ST23, P1, 110
1-Jan-2013, S2, ST23, P2, 121
];

What I want is the following :

initially it should show me

          1-Jan-2013, S1, 1

          1-Jan-2013, S2, 2

if I click the second row (or rather the 1), it should expand into :

     1-Jan-2013, S2, 2, P1

     1-Jan-2013, S2, 2, P2

What's the best way to create this in pivots/charts ?

The actual data set is across a range of dates, all 50 states & multiple stores per state, multiple products per store.

Appreciate any help.

Thanks

MJ 

[khadeer]

PFA. I am not sure u got correct answer r not, Check that one , let me know in what way u want rather than that.

[#3]

Thanks. Teh pivot shows all products.. I wanted to show only the total of the products in each state that has more than one price.

It should look like

     1-Jan, S1, 1          (since there is only one product in S1 that has multiple prices across stores in that state)

     1-Jan, S2, 2          (since there are 2 products in S2 with multiple prices across stores in that state)

Ideally, if I clock the first row, it should expand to show the individual products (P1 in the first case & P1 & P2 in the 2nd case).

[#4]

Thanks. The pivot shows all products.. I wanted to show only the total of the products in each state that has more than one price.

It should look like

     1-Jan, S1, 1          (since there is only one product in S1 that has multiple prices across stores in that state)

     1-Jan, S2, 2          (since there are 2 products in S2 with multiple prices across stores in that state)

Ideally, if I clock the first row, it should expand to show the individual products (P1 in the first case & P1 & P2 in the 2nd case).

[#5]

Thanks. The pivot shows all products.. I wanted to show only the total of the products in each state that has more than one price.

It should look like

     1-Jan, S1, 1          (since there is only one product in S1 that has multiple prices across stores in that state)

     1-Jan, S2, 2          (since there are 2 products in S2 with multiple prices across stores in that state)

Ideally, if I clock the first row, it should expand to show the individual products (P1 in the first case & P1 & P2 in the 2nd case).

[#6]

Thanks. The pivot shows all products.. I wanted to show only the total of the products in each state that has more than one price.

It should look like

     1-Jan, S1, 1          (since there is only one product in S1 that has multiple prices across stores in that state)

     1-Jan, S2, 2          (since there are 2 products in S2 with multiple prices across stores in that state)

Ideally, if I clock the first row, it should expand to show the individual products (P1 in the first case & P1 & P2 in the 2nd case).

[#7]

Thanks. The pivot shows all products.. I wanted to show only the total of the products in each state that has more than one price.

It should look like

     1-Jan, S1, 1          (since there is only one product in S1 that has multiple prices across stores in that state)

     1-Jan, S2, 2          (since there are 2 products in S2 with multiple prices across stores in that state)

Ideally, if I clock the first row, it should expand to show the individual products (P1 in the first case & P1 & P2 in the 2nd case).

[#8]

Thanks. The pivot shows all products.. I wanted to show only the total of the products in each state that has more than one price.

It should look like

     1-Jan, S1, 1          (since there is only one product in S1 that has multiple prices across stores in that state)

     1-Jan, S2, 2          (since there are 2 products in S2 with multiple prices across stores in that state)

Ideally, if I clock the first row, it should expand to show the individual products (P1 in the first case & P1 & P2 in the 2nd case).