I am preparing a bar graph to show the comparison between the current qtr and previous qtr and the comparison is done by week of the respective quarters. If current qtr is 2017Q1 then previous qtr is 2016Q4. Now I want to show the comparison of week in the graph by week. Ex: 2016 W39, 2017 W1, 2016 W40, 2017 W2 etc... but the way the graph is now is W39, W40.... W1, W2... or W1, W2.... W39, W40...
so how can I be able to sort them the way I have explained above i.e. (2016 W39), (2017 W1), (2016 W40), (2017 W2) ....?
Message was edited by: Mannas Jennaa
You can create an inline table in the script with the sort order you want, and join it to your data model.
Then have the object (list box, table, multi box etc.) sort by expression on the field SortOrder
LEFT JOIN (YourTableWithRealValues)
LOAD * INLINE [
2016 W39, 1
2017 W1, 2
2016 W40 , 3
2017 W2, 4
Try using this as your sorting expression:
Year & if(len(mod(purgechars(Week,'Wk'),13))<2,'0'&mod(purgechars(Week,'Wk'),13),mod(purgechars(Week,'Wk'),13))
The expression is assuming that you have a 52-week calendar and that there are 13 weeks per quarter. It is then converting it to a number between 0 and 12.