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

Month, Week, etc. and Year chart dimensions sorting

Tue, 10 Jun 2014 08:13:36 GMT

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

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

Thank you all beforehand for your tips&tricks.

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

Tue, 10 Jun 2014 08:33:39 GMT

Hi Dmitry

please attached your qvw

Chris

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

Tue, 10 Jun 2014 08:56:50 GMT

Hi Chris. Added qvw.

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

Tue, 10 Jun 2014 11:21:45 GMT

Dmitry

use MonthNum instead of MonthAgo in your expression sort

best regards

chris

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

ashfaq_haseeb — Tue, 10 Jun 2014 11:31:54 GMT

Hi,

May be like his

Regards

ASHFAQ

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

Tue, 10 Jun 2014 12:51:07 GMT

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

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

Tue, 10 Jun 2014 12:56:40 GMT

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.).

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

Thu, 12 Jun 2014 09:36:49 GMT

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

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

Fri, 10 Apr 2015 19:35:44 GMT

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.

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

];

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

I hope helps you.