2 Replies Latest reply: May 31, 2012 9:43 AM by Mike Hennigan RSS

    Start Dates

    Mike Hennigan

      Hello,

       

      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!

        • Start Dates
          Oleg Troyansky

          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,

           

          Oleg

            • Re: Start Dates
              Mike Hennigan

              Oleg,

               

              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.

               

              Thanks for your help, it is greatly appreciated!