Hi.
Analyst EP Design question.
I have a list of Projects which can change in size on a monthly basis. This list will eventually be used as my E.list. I need to group these projects in groups of 50 or so, and therefore Im looking to somehow apply a count against each one so i can use a conditional to determine if i have filled one group and now need another, AND THEREFORE A NEW PARENT IN THE ELIST.
Proj A Grp 1 (Count 1)
Proj B Grp 1 (Count 2)
.
.
Proj D Grp 1 (Count 50)
Proj X Grp 2 (Count 51)
Proj Y Grp 2 (Count 52)
.
.
Proj Z Grp 2 (Count 100)
etc etc.
If I could apply a "count" to the projects then i could run a conditional to determine which project belongs in what group.
My main issue is that using a Cumul bif requires a timescale, therefore cumulating the projects along the timescale and not the projects themselves.
Anyone have any ideas about how i can apply a cumulated number sequence against each of my projects?
The solution doesnt need to be pretty. I have just spent far too long on this issue already.
Cheers
Export your project list to txt file and set up an odbc txt driver on it. Then use this odbc dlist updating. In SQL statement you can use normal counts and whole number division to calculate project group number
Hi Thanks for your reply.
Previously I had been discounting a SQL approach as it would further complicate my overall process.
I was hoping to find a solution in Analyst.
I will continue to test a few things before I settle on a SQL approach.
Cheers
Tell us if you'll find anything "simple" — I don't now any Analyst-only simple solution. You can try to pregenerate a cube with 10k lines and then load project names in one dlist item and calculate group number in another. But you'll need to add line number to project name, which is best done in ODBC txt, so it's a tied loop )
Have you tried setting the element only d-List as a time dimension, the Groups as an additional d-List, then setting up an assumption d-cube with a second dimension of three elements:
Subtotal (=1)
Cumulative count (Cumulate Subtotal)
Group formatted element: Conditional Group (if Cumulative count <50, Group1, if {Cumulative count} <100, Group2, etc.
From this assumptions cube you can generate a d-List with the subtotals included, and generate the e-list from that.