6 Replies Latest reply: May 14, 2014 10:39 AM by Tereza Gregorova RSS

    Year to Year comparison if StartYear in September- Set Analysis

    Tereza Gregorova

      Hi all,

       

      I have been fighting with this issue for quite some time now.

       

      I have an application where Year to Year comparison is required. I have a chart and I would like to show how the number of applications for this cycle (2013)  and academic year (2014/5) compare to number of applications from last cycle (2012) and academic year (2013/4).

       

      Every cycle starts in September. So for cycle 2013 there will be actually applications from the calendar year 2013 and 2014.

      I have created in my script:

      CalendarYear (that refers to calendar year starting in January),

      YearChanged (that refers to cycle year starting in September)

      YearOfEntry (that refers to academic year in which the applicant would like to start his/her studies, this is in format 2013/4 so treated like a string)

      WeekChanged (that refers to weeks in the YearChanged where week 1 in this cycle is actually week 38 in calendar year)

      Week (refers to calendar weeks)

       

      Now to that issue

       

      I have created a chart where the first expression is as follow:

       

      =Sum({$<Week={"<=$(=Max(Week))"},YearOfEntry={"=$(=maxstring(YearOfEntry))"}>}Applications)

       

      This works fine and shows what I would expect to see if the particular YearOfEntry is selected.

       

      BUT

      I have also created this second expression that should show the number of applications for the YearOfEntry - 1, YearChanged - 1.

      (example: user will select YearOfEntry 2014/5 which are in cycle YearChanged = 2013, this second expression should therefore bring application for YearOfEntry 2014/3 which are in cycle YearChanged = 2012)

       

      =Sum({$<Week={"<=$(=Max(Week))"},

                     ChangedYear={"=$(=max(ChangedYear)-1)"},

                          YearOfEntry={"$(=text(num(mid(DateMax,1,4))-2) & '/'  &text(num(mid(DateMax,6,1))-2) )"},

                               CalendarYear=>}

                                    Applications)

       

      This does not work and I do not understand why. Please let me know if you have any ideas how to go around this problem!

       

      I know that there is still the option of alternate states, but I would rather solve this by the set analysis then having users have to click around a lot.

        • Re: Year to Year comparison if StartYear in September- Set Analysis
          Marcus Sommer

          I assume the syntax isn't quite correct - simplify for testings your expression through commenting out some parts and/or replacing some expressions with fixed values:

           

          =Sum({$<Week={"<=$(=Max(Week))"},

                         ChangedYear={"=$(=max(ChangedYear)-1)"},

          /*                 YearOfEntry={"$(=text(num(mid(DateMax,1,4))-2) & '/'  &text(num(mid(DateMax,6,1))-2) )"},    */

                              YearOfEntry={2012},

                                   CalendarYear=>}

                                   CalendarYear=>}

                                        Applications)

           

          - Marcus

            • Re: Year to Year comparison if StartYear in September- Set Analysis
              Tereza Gregorova

              Marcus, the YearOfEntry is a string and the values looks like eg: 2014/5. Also you are suggesting to select only YearOfEntry=2012. This would not help as I want this to be dynamic and to change based on user selections. This would always compare only the selected YearOfEntry with YearOfEntry=2012 (if that was actually valid value).

               

              In my application users can select from YearOfEntry 2015/6, 2014/5, 2013/4,2013/2 ....

               

              I have also tried to comment some parts of the expression, also tried different combinations but still not luck!

               

              Thank you for your help anyway!