7 Replies Latest reply: Jan 25, 2013 8:22 AM by M J RSS

    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 

        • Re: Help with Pivots/Dimensions
          khadeer basha

          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.

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

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

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

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

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

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