Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Month, Week, etc. and Year chart dimensions sorting

Hello everybody.

I have an issue with a very easy task, please help.

I can't get correct sorting in bar charts with 2 separate dimensions (1st dim - Week, Month, Quarter) and 2nd dim - Year for last Quarters or 6 Months or etc.

It's easy to do through 1 year, but when data goes to the next year, I get full mess on chart (e.g. Nov-Dec-Jan-Feb transforms into Jan-Feb-Nov-Dec). I thought, that its a natural sort thing for Qlikview, but I spent now more than week to make it happen and still no luck...

The problem is in attached file with 3 examples.

I need sorting in chronological way. Now is june 2014, I compare this value with June 2013, then previous 6 or more months in chronological way, so it should look like this:

Previous year 2013 - Nov(comparing 2012 and 2013, there is no data for Nov 2014)

Previous year 2013 - Dec(2012 and 2013)

This Year 2014 - Jan(2013 and 2014)

Feb(2013 and 2014)

......

Jun(2013 and 2014)

For Quarters: Prev.Year 2013- Q4 (2012 and 2013), then this year 2014 - Q1, Q2 (2013 and 2014), because there is still no data for Q4 2014.

For weeks: prev. year - 46,47,49...52, this year - 1,2,3,4...24

I have a master calendar with different synthetic sort fields, but when I'm trying to apply it in sort tab nothing happens (e.g. for Months):

12*(Year(Today())-Year(date_wb_start)) + Month(Today()) - Month(date_wb_start) as MonthsAgo,

Month(date_wb_start)+Year(date_wb_start)-Year($(vMinDate))+($(vQYears)-1)*(Month(date_wb_start)-1) as MonthSort

Sort.jpg

I'm doing something wrong, but can't understand what exactly.

Thank you all beforehand for your tips&tricks.

Tags (4)
8 Replies
Not applicable

Re: Month, Week, etc. and Year chart dimensions sorting

Hi Dmitry

please attached your qvw

Chris

Not applicable

Re: Month, Week, etc. and Year chart dimensions sorting

Hi Chris. Added qvw.

Not applicable

Re: Month, Week, etc. and Year chart dimensions sorting

Dmitry

use MonthNum instead of MonthAgo in your expression sort

best regards

chris

Re: Month, Week, etc. and Year chart dimensions sorting

Hi,

May be like his

Regards

ASHFAQ

Not applicable

Re: Month, Week, etc. and Year chart dimensions sorting

Thank you for your quick answers!

I've modified my qvw for better understanding what I'm trying to do. Sorry my English is not good.

This kind of sorts (ascending numeric values or months or weeks, etc.) can be done without sort expressions, but I need sorting in chronological way. Now is june 2014, I compare this value with June 2013, then previous 6 or more months in chronological way, so it should look like this:

Previous year 2013 - Nov(comparing 2012 and 2013, there is no data for Nov 2014)

Previous year 2013 - Dec(2012 and 2013)

This Year 2014 - Jan(2013 and 2014)

Feb(2013 and 2014)

......

Jun(2013 and 2014)

For Quarters: Prev.Year 2013- Q4 (2012 and 2013), then this year 2014 - Q1, Q2 (2013 and 2014), because there is still no data for Q4 2014.

For weeks: prev. year - 46,47,49...52, this year - 1,2,3,4...24

Not applicable

Re: Month, Week, etc. and Year chart dimensions sorting

It will sort in numeric order, but I need chronological order. Previous year, then this year (2013Y weeks or months then 2014 weeks, months, quarters, days, etc.).

Not applicable

Re: Month, Week, etc. and Year chart dimensions sorting

Hi Dmitry

so the sort has to be on value "Week-Year" in your script then load it ascending and sort it by Load order

best regards

chris

Not applicable

Re: Month, Week, etc. and Year chart dimensions sorting

Hi Dmitry,

One quick way is to add the below Inline table with 2 columns:

MonthSort:

LOAD * INLINE [

MonthSort, Month

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, Jun

7, Jul

8, Aug

9, Sep

10, Oct

11, Nov

12, Dec

];

Month Column points to the main data table like in the attached table.

Month Sorting.jpeg

Main Data Table will be like the below one:

Sales:

Load * Inline [

Year, Month, NoOfIncidents, Quarter, Severity, Severity_Level

2013, Jan, 5, Q1, Very Important, 5

2013, Feb, 3, Q1, Very Important, 5

2013, Mar, 3, Q1, Very Important, 5

2013, Apr, 10, Q2, Critical, 4

2013, May, 3, Q2, Critical, 4

2013, Jun, 1, Q2, Critical, 4

2013, Jul, 1, Q3, Very Important, 5

2013, Aug, 7, Q3, Very Important, 5

2013, Sep, 10, Q3, Critical, 4

2013, Oct, 5, Q4, Very Important, 5

2013, Nov, 10, Q4, Very Important, 5

2013, Dec, 3, Q4, Critical, 4

2014, Jan, 5, Q1, Very Important, 5

2014, Feb, 1, Q1, Critical, 4

2014, Mar, 9, Q1, Critical, 4

2014, Apr, 3, Q2, Very Important, 5

2014, May, 3, Q2, Critical, 4

2014, Jun, 10, Q2, Critical, 4

2014, Jul, 4, Q3, Critical, 4

2014, Aug, 7, Q3, Critical, 4

2014, Sep, 1, Q3, Very Important, 5

];

Month Sorting with Expression under Sort Page.jpeg

This should automatically sort the months on the charts without any problem.

I hope helps you.

Community Browser