Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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).
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).
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).
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).
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).
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).