Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Dmitry
please attached your qvw
Chris
Hi Chris. Added qvw.
Dmitry
use MonthNum instead of MonthAgo in your expression sort
best regards
chris
Hi,
May be like his
Regards
ASHFAQ
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
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.).
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
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.