Using Pivot Table as Datasource

[#1]

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 .

[#2]

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

[#3]

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.

[#4]

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

[#5]

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?

[#6]

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

[#7 · Accepted solution]

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.

[#8]

thanks again for the fast reply

basicly,

(2) =sum({<Salesarea={(3)+1}>} balance) //is the same as using

(2) =sum({<Salesarea={4}>} balance) // right ?

so basicly, its like:

(2) =sum({<Salesarea={fixedvalue}>} balance)

is the following possible ?

(2) =sum({<Salesarea={(CURRENTvalue)+1}>} balance)

so when the table shows salesarea "3", the salesarea+1 in the formula should be "4"

and if the table shows "4" the salesare+1 in the formula should be "5"

[#9]

i found a solution:

=sum({<Salesarea={$(variable)}>} balance)

im now changing the variable thruough an external function & if necessary, an input field

thank you marcsliving

alex