topic Re: Create Vairance column in Pivot Table in App Development

Create Vairance column in Pivot Table

43918084 — Mon, 20 Dec 2021 21:32:42 GMT

I have created a pivot table with 2 periods and want to add Variance column to show difference between previous period vs current period.

I have created a set analysis formula like below, but I do not get the correct result.

=(Sum({<[ACCTG DATE (P0045)]={$(=max([ACCTG DATE (P0045)]))}>}[FUNCTIONAL AMT(YTD)])
- Sum({<[ACCTG DATE (P0045)]={$(=min([ACCTG DATE (P0045)]))}>}[FUNCTIONAL AMT(YTD)]))

The result I want is

	Function	Function	Var
ACCTG DATE (P0045)	20191231	20200131	20200131 vs 20191231
Status	20,191,231	20,200,131	8,900
Totals	(19,510,795)	(19,304,099)	206,696
Exist	(19,510,795)	(19,304,099)	206,696

But I get an incorrect result like below. Hope I can get some guidance

	Function	Function	Var	Var
ACCTG DATE (P0045)	20191231	20200131	20191231	20200131
Status	20,191,231	20,200,131	20,191,231	20,200,131
Exist	(19,510,795)	(19,304,099)	19,510,795	(19,304,099)

Re: Create Vairance column in Pivot Table

tresB — Thu, 05 Mar 2020 08:17:12 GMT

Have you tried like:

Var = Column(1) - Column(2)

Re: Create Vairance column in Pivot Table

43918084 — Thu, 05 Mar 2020 08:26:36 GMT

Thank you very much. When I use your suggested formula, the result becomes "-"

	Function	Function	Var	Var
ACCTG DATE (P0045)	20191231	20200131	20191231	20200131
Status	20,191,231	20,200,131	20,191,231	20,200,131
Exist	(19,510,795)	(19,304,099)	-	-

Re: Create Vairance column in Pivot Table

tresB — Thu, 05 Mar 2020 08:29:56 GMT

Could you share a sample app to check?

Re: Create Vairance column in Pivot Table

43918084 — Thu, 05 Mar 2020 08:37:40 GMT

Sure thank you very much for your immediate help. Sorry I don't know how to attach the file here. Hope you would not mind to advice.

Re: Create Vairance column in Pivot Table

tresB — Thu, 05 Mar 2020 08:48:18 GMT

Click on the clip symbol on the left bottom corner of edit window.

Re: Create Vairance column in Pivot Table

43918084 — Thu, 05 Mar 2020 09:05:33 GMT

I am really sorry for my inexperience. There is no Clip symbol in my screen.

Re: Create Vairance column in Pivot Table

tresB — Thu, 05 Mar 2020 09:22:38 GMT

This?

Re: Create Vairance column in Pivot Table

43918084 — Thu, 05 Mar 2020 09:45:26 GMT

Thanks a lot for your patience. Somehow the Clip icon is not visible in my screen. I have to put my mouse around to find it. I have attached the qvf file here. The Pivot table is under YTD Variance (Based on SAL)

Thank you very much again

Re: Create Vairance column in Pivot Table

tresB — Thu, 05 Mar 2020 10:16:48 GMT

Since you have multiple members (dates) in column - you would get multiple outputs for an expression (and NOT one -unlike you want). To get one variance column, you have to modify your script I believe.

Re: Create Vairance column in Pivot Table

43918084 — Thu, 05 Mar 2020 10:27:58 GMT

Thank you very much. Do you mean modifying the script in Script editor? Or I need to remove some of the fields in the left hand side of the pivot that were collapsed ?