Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
dhasharadh
Creator III
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)
1 Solution

Accepted Solutions
rubenmarin

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

View solution in original post

3 Replies
rubenmarin

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

dhasharadh
Creator III
Creator III
Author

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

rubenmarin

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.