Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Help with Pivots/Dimensions

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 

7 Replies
khadeer_sparks
Valued Contributor

Re: Help with Pivots/Dimensions

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.

Not applicable

Re: Help with Pivots/Dimensions

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).

Not applicable

Re: Help with Pivots/Dimensions

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).

Not applicable

Re: Help with Pivots/Dimensions

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).

Not applicable

Re: Help with Pivots/Dimensions

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).

Not applicable

Re: Help with Pivots/Dimensions

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).

Not applicable

Re: Help with Pivots/Dimensions

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).