Author |
Message |
marz
Newbie
Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
|
Topic: Distinct Sum Posted: 15 Mar 2011 at 4:29am |
Trying to figure out a way to get the "ValueSumFormula" below. Basically it is a sum of the "Value" using distinct ID2 grouped by ID1. Do i make sense? But here's exactly what it needs to look like...
ID1 ID2 Value ValueSumFormula z a 5 8 z b 3 8 z a 5 8 z b 3 8
x e 4 6 x f 2 6 x e 4 6 x f 2 6
Thoughts?
Thanks.
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 16 Mar 2011 at 3:30am |
so why not put in groups for id1 and id2 then use sum({table.Value}, {table.ID2})?
|
IP Logged |
|
marz
Newbie
Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
|
Posted: 16 Mar 2011 at 6:01am |
because then I'd get the wrong ValueSumFormula...
ID1 ID2 Value ValueSumFormula z a 5 10 z a 5 10
z b 3 6 z b 3 6
x e 4 8 x e 4 8
x f 2 4 x f 2 4
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 16 Mar 2011 at 7:15am |
ok, then you are going to need to write a formula that will do all the summing for you as well as keeping track of which values have already been added to the sum.
somethingl like:
shared stringvar seen;
shared numbervar seensum;
local stringvar this :="|"+{table.id2}+"|";
if instr(seen, this ) = 0 then(
seensum := seensum + {table.Value};
seen := seen + this;
);
"" //hid the result
you will need a reset and display formula as well
|
IP Logged |
|
marz
Newbie
Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
|
Posted: 16 Mar 2011 at 11:59am |
Ok I almost understand. Here's what I have:
shared stringvar seen; shared numbervar seensum; local stringvar this :="|"+ ToText({RENTALQTYADJUSTMENT.RENTALQTYADJUSTMENT_ID})+"|"; if instr(seen, this ) = 0 then( seensum := seensum + {RENTALQTYADJUSTMENT.QUANTITY}; seen := seen + this; );
What this returns is the:
"|"+ ToText({RENTALQTYADJUSTMENT.RENTALQTYADJUSTMENT_ID})+"|";
...on exaclty the right fields to summaryize only once. I'm guessing now I need to create a summary field that only summarizes on those? .. but I can't figure out how to do that because a Running Rotal feld doesn't allow me to evaluate on other formulas.
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 17 Mar 2011 at 3:06am |
you can suppress the return value by adding "" after the snippet...CR returns whatever is the last value it sees.
formulas will do whatever you like, and any time...Running totals have their limitations, and their advantages. Think of when your summary should reset, and you can add another shared variable to keep the summary value until it is time to display/reset.
HTH
|
IP Logged |
|
marz
Newbie
Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
|
Posted: 17 Mar 2011 at 7:37am |
But how do I make your formula above show a sum of the value field instead of showing the "|"+{table.id2}+"|" ?
I'm a little lost with it.
Thanks.
|
IP Logged |
|
marz
Newbie
Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
|
Posted: 21 Mar 2011 at 9:07am |
Ok so the below works but how do I get it to reset at change of ID1?...
shared stringvar seen; shared numbervar seensum; local stringvar this :="|"+ ToText({RENTALQTYADJUSTMENT.RENTALQTYADJUSTMENT_ID})+"|";
if instr(seen, this ) = 0 then( seen := seen + this; seensum := seensum + {RENTALQTYADJUSTMENT.QUANTITY} )
Thank you.
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 22 Mar 2011 at 3:10am |
make a formula like
shared numbervar seensum :=0;
shared stringvar seen := "";
"" //hides the reset
place the formula in the group header for ID1...then the variables are reset.
HTH
|
IP Logged |
|
marz
Newbie
Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
|
Posted: 22 Mar 2011 at 5:25am |
Got it. Thanks for all your help! I think I'm done with this one :-)
|
IP Logged |
|
|