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
Hej Ole
Kan jeg få den til at tage højde for at månederne også?
I dit salesperson eksempel:
Item Armani, Sand S bliver også solgt i juni, men der skal den acc sum starte forfra?
Auto translation:
Can I make it take into account the months as well? In your salesperson example: Item Armani, Sand S is also sold in June, but the acc sum have to start over...?
Answer:
Hi Jane,
Using my example, you will have to add a Month dimension as the second dimension on the vertical axis. The Month dimension may replace the Date dimension, or you can keep both as long as the Month dimension comes right after the Item Name dimension.
If keeping both the Month dimension and the Date dimension, the calculation syntax would then be:
Rowcount level 2: allcount(d1, d1:0(l(1,1,0,0)), m1)
BR / Ole
Please sign in to leave a comment.