I'm trying to display student count information. I would like to show the school, count of new students for the selected start period and the number of students for the previous start period. The only problem is the previous start period is not always the last start period.
Here is what my start periods look like:
ID Date
1 09/01/2011
2 10/10/2011
3 01/10/2012
4 01/30/2012
5 02/07/2012
6 03/08/2012
7 03/13/2012
One of my schools might only have the following start periods:
ID Date
3 01/10/2012
5 02/07/2012
7 03/13/2012
So if I select this school and start period 02/07/2012, I would want to show the the student count information for periods 02/07/2012 and 01/10/2012 (not 01/30/2012 because that start period doesn't exist for the selected school). I hope this makes sense. Any help would be greatly appreciated!
The problem here is that you will need to filter different periods for different Schools, which makes Set Analysis selection of the Period impossible (Set Analysis can't be sensitive to individual dimension values).
The following solution might be an over kill, but this is the only thing that comes to mind...
You need to separate the Start Period in your Data table from the Start Period that is available for selection, into two different fields, and link the two fields based on their "Current" and "Prior" period relation. Since the last periods can be different between schools, the School number has to be part of this relation.
So, you'll need to load a distinct list of all Schools and the corresponding Start Periods. Then, for each Shool and Each period, you can determine the "Previos" period. Then, create a "link table" that connects your original "Transaction Date" with the newly created "Selection Date" and two flags - "Current Period Flag" and "Prior Period Flag". The link table will look like this:
1. School ID - Period ID concatenated key (avoiding synthetic keys)
2. Selection Period
3. Current Month Flag
4. Prior Month Flag
You will need to create the same concatenated key in your data table, to link your School ID/Period ID to the Link Table.
Once all of it is in place, you can use the Selection Date in your List Boxes for selections and use the flags in your Set Analysis:
Current Period Student Count = sum({<CurrentMonthFlag={1}>} Counter)
Previous Period Student Count = sum({<PriorMonthFlag={1}>} Counter)
Notice that once you "disconnect" the Period in the Data and the Period on the screen, you need to use this kind of a Set Analysis in all of your expressions.
I know it might be a bit too complicated, but it should do the job,
Thanks for the suggestion. I ended up doing something similar to what you suggested where I concatenated and ordered the data by school and start period date then assigned a period id so my date data looks like this:
KEY_StartDate StartDate StartDateID
A-40787 09/01/2011 1
A-40826 10/10/2011 2
B-40918 01/10/2012 3
B-40946 02/07/2012 4
B-40981 03/13/2012 5
I linked it to my main data table through the KEY_StartDate.
To get my student counts I used the following formulas:
count(DISTINCT StudentNumber) /* current period */
count({1<School=p(School),StartDateID={$(=(StartDateID)-1)}>} DISTINCT StudentNumber) /* previous period */
I chose not to use flags to make the dashboard more flexible.
