Sorting of time hierarchy in cronological order

[#1]

Hi All,

Thanks for all your help in advance, I'm using this blog http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/11/unbalanced-n-level-hierarchies to create time hiererchies which was very helpful and was able to create hiererachies using all the code and work my way through.

I have a Calendar table called TimeTrans creates using hierechy to load all the data. Everything is working fine except the sorting as the business users want it to be sorted as Current 13 Weeks, Weeks, Month, Quarter, Season and then by Year in cronological order which I'm trying to modify in the code itself the way we create Nodes1...N, so it dynamically sorts depending on user selection.

For example: If Q3-14, Q4-14, Aug-14 and Nov-14 and Current 13 Weeks has been selected, then the report should display as Current 13 Weeks, Q3-14, Aug-14, Q4-14, Nov14, in cronological order. Same is true for any selection.

Current 13 Weeks hold current 13 weeks data, which has depth 6 and Displaylevel is Yes.

Node is Nametime, and Node1...n is Nametime1...Nametime6, Node Path is Pathtime in my code.

Nametime is the dimension for time selection on x axis used to create the dynamic selection of the pathtime as per attached document:

For some reason the sorting is not working and was looking for help if you or anyone could help resolve this issue which would be game winner.

I need your expertise to help me solve this sorting of calendar table in hierechy pathtime, as sorting doesn't work to sort the data and would like to see if there's any alternative solution to this.

Again thanks in advance for all your help and looking forward to getting help in solving this issue. Please let me know if you need any other information.

Thanks,

DD

[jagan]

Hi,

Can you post the script with which you are creating the Week, Quarter and Month fields, you can use Dual to arrive the numeric dates for this string Date fields.

Try like this

LOAD

*,

Dual('Q' & Ceil(Month(DateDimensionName)/3) & '-' & Date(DateDimensionName, 'YY'), QuarterStart(DateDimensionName)) AS Quarter,

Date(DateDimensionName, 'MMM-YY')  AS Month,

FROM DataSource;

Now for month and quarter select Number in Sort tab.

Hope this helps you.

Regards,

Jagan.