8 Replies Latest reply: Sep 2, 2011 8:07 AM by Andreas Dippel RSS

    Using Pivot Table as Datasource

      Good Evening (also goodmorning, depending on where you live)

       

      i was trying to use the results of formulas and other data manipulations from a pivot table in other calculations.

      is it possible to fe. use the results from a pivot or straight table to display a graphic ?

       

      thank you

      alex

       

      Thats right, i want to re-use the results i got in a straight/pivot table.

      because of the functions used in the table, it generates new results, which i want to use in diagrams or other tables.

      this should be done without copying the formula from the table to the new diagram.

       

      Text added by Alexander .

        • Using Pivot Table as Datasource

          Hi Alexander

           

           

          It's my bad i didn't got you can you be more specific regarding your issue .

          you said you want to re-use the results you got in a straight/pivot table to show some other graphic charts or is that totally different ...

           

           

          thanks

          Meher

          • Using Pivot Table as Datasource

            If you want to know the specific value for a dimension you can use set analysis and use that in other places.

             

            Example:

             

            Top Income Balance

            Sales               155

            COGS             100

            GenAdmin        122

            Selling                133

             

            =sum({<Topincome={"Sales"}>} balance)

             

            this would return just 155.

              • Re: Using Pivot Table as Datasource

                @marcsliving,  im not shure if i understand you right

                 

                i was thinking about your approach, but im not shure if its possible to use your approach to extract data from a straight table.

                 

                its not about trying to extract data from a database table, the goal is to read data results from functions or other data from a straight/pivot table. the data i want to read/use is "produced" in the table.

                  • Using Pivot Table as Datasource

                    So you want to use the result of a formula from the pivot in other places correct?

                     

                    Using set analysis would capture the result of the formula as it would in the table. You just need to specify the dimensions in the formula.

                     

                    In my example it is all from one table using sum(balance) on the pivot/straight table breaks it down by dimension. I needed to use just the total of Sales in a formula somewhere else. Using set analysis let me specify that I only want the balance if the group is Sales. If you have more than one dimension you can specify it in the set analysis to drill down the results even more.

                     

                    If I am still not understanding correctly could you give more examples of what you are trying to do?

                      • Re: Using Pivot Table as Datasource

                        thanks for your suggestion.

                        this function works, but only, if you use 1 condition for choosing the values from the table.

                        if the condition has to change, the function does not work. f.e.

                        (1) =sum({<Salesare={"3"}>} balance) //works

                        (2) =sum({<Salesare={(Salesarea)+1}>} balance) // does not work (to compare the current and the next unit)

                         

                        so, if the condition has to be a formula itself, this way does not work.

                        is it possible to use something like that with an function as a condition (like in (2))?

                         

                        Thank you,

                        Alex

                          • Re: Using Pivot Table as Datasource

                            I believe that

                            (2) =sum({<Salesare={(Salesarea)+1}>} balance) // does not work (to compare the current and the next unit)

                             

                            does not work, because you do not define a salesarea to add one to.

                             

                             

                            {(Salesarea)+1} this is basiacaly saying (say you have 5 sales areas)

                            {(1,2,3,4,5)+1} which is what is probably causeing the issue.

                             

                             

                            So  defining the current sales area you can then add 1 to see the next value.

                            (2) =sum({<Salesare={(3)+1}>} balance)

                             

                            here you are saying return the balance only if the salesare= 4

                             

                            Note: If you number 2 was used in a pivot/straight table it may work if the dimension is sales area. Would need to test to see.