formulasgoogle sheetspivot table
Is it possible to use the results of a calculated field as part of a new calculated field? I know further calculation could be done outside the pivot table but I was hoping there was a way to somehow do this within the pivot table.
Appreciate your feedback!
I don't, except I suspect you are out of luck for the time being at least. However, even in Excel I have often found it as, or even more, useful to perform such grouping in the base data rather than in the pivot table. For me the summary provided by the table is often a prelude to drilling down for further detail and rather than end up then with many different extracts I find it much easier to filter the source data, which I have already formatted to suit me.
On that basis a hack of sorts may serve for you. Assuming data as in D:E in the image, I added three columns with formulae:
=year(D2) =month(D2)=weeknum(D2)To reduce the need for recalculation I usually then convert the results to values.
ColumnsA:C can then be included in the pivot table and, unlike Grouping, the components of the date can be split between rows and columns in the pivot table:
It appears you were trying to enter 'SUM of Transations' and so on into the formula. But "SUM of" if not a part of the name, it's just an indication of how the column was summarized. The following works in the calculated formulas:
=Transactions/Sessions
Since those columns are summarized using SUM, you will get the sum of transactions divided by the sum of sessions.
Quotes are not necessary here since you don't have spaces in column names.
Best Answer
Accessing the values of one calculated field in another such field from the Pivot Table menu doesn't seem possible.
Here are two facts that were encouraging, but I suspect are dead ends:
Regardless of trying to reference pivot table headers local to the workbook or as absolute values, I wasn't able to make it cooperate.
As another misfortune, you can't create a table which explicitly references another. So while you can create a new column with cells referencing cells, any expansion or contraction of the table may cause those outside cells to become inaccurate.