cancel
Showing results for
Did you mean:
Creator III

Help with Pivot table

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.

Labels (2)

• General Question

1 Solution

Accepted Solutions
MVP

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

3 Replies
MVP

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

Creator III
Author

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

MVP

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.