Accumulated sum on dimension value
I need some help to solve the following issue:
I would like to change the last column "Akk.sum" (standard calculation in Targit), so it starts from zero each time I have a new "Item next level"
So in the tabel below I would like the values to be:
Line 1: 75
Line 2: 200
Line 3: 200
Line 4: 50
Line 5: 100 (50+50)
Line 6: 100
0
Comments
Maybe this will work:
Akk.sum = sum(d-1, d1:0(s), m1)
Hi Louise
No, it didn't work out. I think there must be a parent child relation to use siblings (thanks anyway)
Hi Flemming,
I did it like this:
Rowcount level 1: allcount(d1, d1:0(l(1,0,0)), m1)
(you may need more ",0,0,0" according to the number of dimensions)
New Calculation: if sum(c1, 0, 0) <> sum(c1, -1, 0, 0) then sum(d1, 0, m1) else sum(c2, -1, 0, 0) + sum(d1, 0, m1)
BR / Ole
Hi Ole
Thank you - you solved my issue ;-)
Hi,
I also have a challenge with the Acc Sum. I want the acc sum from m1 (transactions) stops at the last date of a customer and then the customer balance start again with a new customer. Now Acc sum keeps counting, so the next customer has a wrong balance (should be 0 instead of 1,029,631,10). Below the screenshot with formule and the crosstab. Thanks!
Hi Frank,
I think you should be able to use the same method as in my reply above.
However, based upon your screenshot, the first calculation may need an additional zero.
Rowcount level 1: allcount(d1, d1:0(l(1,0,0,0)), m1)
BR / Ole
Thanks Ole! With this calculation I now have a new number per customer (customer A = 1, customer B = 2). But how do I integrate the acc sum formula sum(0, d1:0(v), m1) so that only the sum per customer is calculated? The formula New Calculation: if sum(c1, 0, 0) <> sum(c1, -1, 0, 0) then sum(d1, 0, m1) else sum(c2, -1, 0, 0) + sum(d1, 0, m1). gives the same values as the d1.
d1 is the transaction value, c1 is the acc sum (customer balance) and c2 is now the row count.
thanks!
Hi Frank,
My example is based on the row count being the first calculation, c1, and the acc sum being the second calculation, c2.
So you should probably just change the order of your two calculations to make it work.
BR / Ole
Please sign in to leave a comment.