topic Re: Help with Pivot table in App Development

Help with Pivot table

dhasharadh — Fri, 08 Jul 2022 16:05:25 GMT

Hi All, I need some help on the below. red once are the required outputs. @sunny_talwar @swuehl

YearMonth	202201	202202	202203	202204	202205
Adds	1000	2000	1500	2000	2000
Delete	500	1000	1000	1000	900
Base	500	1500	2000	4000	5100
Avg Base	225	1000	1750	3000	4550

Final = Previous Month Base + Adds - Delete , Ex : for 202201 previous month base is 0 + 1000(adds) - 500 (Delete)

Avg Base = (Previous Month Base + Curent Month Base)/2. Ex: for 202201 (0 + 500)/2.

Re: Help with Pivot table

rubenmarin — Sat, 09 Jul 2022 07:39:24 GMT

Hi, if Adds is "Sum({<Type={'Adds'}>} Amount)" and Delete is "Sum({<Type={'Delete'}>} Amount)"

- Base could be: RangeSum(Before(Sum({<Type={'Adds'}>} Amount)-Sum({<Type={'Delete'}>} Amount), 0, ColumnNo()))

- And the Avg Base: (RangeSum(Before(Sum({<Type={'Adds'}>} Amount)-Sum({<Type={'Delete'}>} Amount), 0, ColumnNo()))
+RangeSum(Before(Sum({<Type={'Adds'}>} Amount)-Sum({<Type={'Delete'}>} Amount), 0, ColumnNo()-1)))/2

Re: Help with Pivot table

dhasharadh — Sun, 10 Jul 2022 14:51:58 GMT

Many Thanks @rubenmarin for Base the expression is working, but for Avg Base is not working as expected.

Re: Help with Pivot table

rubenmarin — Mon, 11 Jul 2022 10:27:58 GMT

Hi, and what is the expected result? in the example it only does a division of that column with the previous, and that's what the expression does.