Topic: Array Formula Posted: 04 Sep 2020 at 8:47am
All,
I'm having trouble finding a way to do what I want in a group.
I currently have a group list of customer names (we'll say ("AA", "BB", "CC") for example).
I have another group above that that shows invoicing date by month so it looks kind of like this
July 2020
"AA"
"BB"
"CC"
August 2020
"AA"
"CC"
"DD"
"EE"
In the above case, customer "AA" and "CC" were invoiced July and August, "BB" dropped after July and "DD" and "EE" were added in August.
What I'm trying to find a solution for is how to show a count of how many customers were dropped or added in any given month (aka August would show +2 adds, -1 drop). I feel like an array with formulas in the header, details and footer would do this, I just don't know where to go with it.
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Posted: 09 Sep 2020 at 5:27am
My solution is to use shared variables and strings. You stated that there is a grouping by the month. I would put something like:
shared stringvar thisMonth := "";
""
in the group header. this initializes which entries were added this month.
In the detail, my formula would look something like this:
shared stringvar thisMonth;
shared stringvar overall;
if instr(overall, {table.field})=0 then(
thisMonth := thisMonth + {table.field};
overall := overall + "," + {table.field};
);
""
This finds the new/added entries, and updates what the list of entries is.
Finally, in the group footer, we need to find the drops:
shared stringvar overall;
shared stringvar thisMonth;
shared stringvar dropped := "";
local stringvar array allEntry := split(overall, ",");
local numbervar i;
for i=1 to ubound(allEntry)( //I don't remember the syntax off the top of my head
if instr(thisMonth, allEntry) = 0 then(
dropped := dropped + "," + allEntry;
allEntry := "";
)
);
overall := join(allEntry, ",");
""
this will find the dropped and reset the list to start the next month.
Finally in the displays it would be simply:
shared stringvar thisMonth //this shows the adds
This should give a roadmap of how I would approach the issue. It should work. It might need some tweaking.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum