Year to Year comparison if StartYear in September- Set Analysis

[terezagr]

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.

[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

[terezagr]

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!

[marcus_sommer]

2012 was only meant as example for a fixed value. If it's a string it must be like YearOfEntry={'2014/5'}. Therefore {"$(=text(num(mid(DateMax,1,4))-2) & '/'  &text(num(mid(DateMax,6,1))-2) )"} must return a string and needed single quotes:

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

- Marcus

[terezagr]

Marcus, according to Qlikview 11 for Developers book single quotes should be used for Text Value, but double quotes for SearchString, that is why I used the double quotes...

Also, when I tried the single quotes, it did not return the expected result and it indicated that there was an error in expression. (although, I know very well that Qlikview is being weird in this case and saying Expression OK, but then it actually highlight the error in the code)

[marcus_sommer]

Had YearOfEntry={'2014/5'} worked? If not the problem is elsewhere. If yes the expression {'$(=text(num(mid(DateMax,1,4))-2) & '/'  &text(num(mid(DateMax,6,1))-2) )'} returned not the right value and/or format.

Put text(num(mid(DateMax,1,4))-2) & '/'  &text(num(mid(DateMax,6,1))-2) in another expression to see the result - perhaps the text()-function caused the error then it won't needed.

- Marcus

[terezagr · Accepted solution]

Marcus, I have tested this again with related numbers and then build the expression again and it worked!

The final expression that worked is as follow:

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

               YearChanged={'$(=max(YearChanged)-1)'},

                        Year={$(=max(Year)-1),$(=max(Year)-2)}>}

                              Applications)

Marcus thank you for your kind help!

T.