topic Re: Mini Charts and Line Charts in QlikView

Mini Charts and Line Charts

Anonymous — Sun, 02 Nov 2014 22:17:45 GMT

I have created a line chart and a mini chart for trending using the same expressions. However the trending line in the mini charts is totally different from the one in the line chart. What could be the cause of this difference?

Regards.

Chris

Re: Mini Charts and Line Charts

danieloberbilli — Sun, 02 Nov 2014 23:06:54 GMT

Please upload the qvw or at least a screenshot of both objects that also shows the used dimensions/expressions

Re: Re: Mini Charts and Line Charts

Anonymous — Mon, 03 Nov 2014 00:03:47 GMT

Dear Daniel

Please find herewith screen shots of requested information. The dimension in all charts is month. The line charts at the top should be the same with the two mini charts below for the current selections.

Regards.

Chris

Re: Mini Charts and Line Charts

danieloberbilli — Mon, 03 Nov 2014 00:24:45 GMT

Could it be, that you also have the cyclic group (Month, ...) in the straight table and that it just shows the results of the other dimension of the cyclic group?

Re: Mini Charts and Line Charts

Anonymous — Mon, 03 Nov 2014 07:05:37 GMT

Many thanks, the cyclic group is in the line chart and consists of month, quarter and year. It is not in the straight table.

Regards.

Re: Re: Mini Charts and Line Charts

danieloberbilli — Mon, 03 Nov 2014 08:22:31 GMT

the only idea I have is to check again if the dimension in the mini chart settings (see screenshot) is really the same month-field that is defined within the cyclic group. It may be that you are dealing with several month-fields and that the month field in the cyclic group is just labeled as 'Month' but contains another field.

Re: Re: Re: Mini Charts and Line Charts

Anonymous — Mon, 03 Nov 2014 12:27:17 GMT

Daniel, thank your very much for your contributions so far. I have checked the issues raised and I do not seem to find any problem (see attached). The group settings is using Month with a default field label.

regards.

Re: Mini Charts and Line Charts

kuba_michalik — Mon, 03 Nov 2014 12:52:49 GMT

Months on the minichart seem to be sorted in descending order. Try playing with Month field sort order in Document Properties (as you can't customize dimension sort order in the minichart itself).

Re: Mini Charts and Line Charts

danieloberbilli — Mon, 03 Nov 2014 12:57:33 GMT

I think Jakub is right...if you compare the lines it seems that the sorting is going from July to January

Re: Re: Re: Mini Charts and Line Charts

Anonymous — Mon, 03 Nov 2014 13:13:15 GMT

Dear Daniel

I think i have figured out what is happening. The line chart trend line is sorted from January through to July in the right sort order and presenting the right trend line. The mini chart month is sorted in reverse order from July through to January from my observation when i changed the mini chart mode from line with dots to bars. How do i sort the mini chart to give me the correct trending?

regards.

Re: Re: Re: Mini Charts and Line Charts

danieloberbilli — Mon, 03 Nov 2014 13:21:03 GMT

you could use sort order in your script like

LOAD

...

Resident xyz Order by Month asc;

or (what I consider as best practice): load the Field a first time just as a dimension in the right order and drop it at the end of the script:

SortingOrderMonth_tmp:

LOAD Month FROM XYZ Order by Month asc; // or its already in the right order in your reference file (which could be a simple inline table with the values January to July

then load your 'real' data LOAD ... FROM ...

and at the end:

Drop table SortingOrderMonth_tmp;

the effect is that QV takes the sorting order from the first time the field is loaded and applies the order on the table with the real data.

Re: Mini Charts and Line Charts

kuba_michalik — Mon, 03 Nov 2014 13:26:37 GMT

As an aside, if you don't want to change things at script level, change field sort order for Month on the Document Properties -> Sort tab. If you do change it at script level (which is indeed good practice for performance reasons), still check the sort order there, in case something is overriding the Load Order option.

Re: Re: Mini Charts and Line Charts

Anonymous — Mon, 03 Nov 2014 13:28:12 GMT

Many thanks, i changed the sort properties in the mini chart from descending to ascending under the numerical values check box and all is fine now.

Thank you very much all

Chris

Re: Re: Re: Re: Mini Charts and Line Charts

Anonymous — Mon, 03 Nov 2014 18:48:18 GMT

Dear Daniel

Please find below my calendar script where the month dimension resides. I have tried to implement your recommendations with the script at the bottom of my script (SortingOrderMonth_Temp) which i have commented out as there is a script error after running it. Can you kindly assist. Where should i place the the script within my calendar script.

regards.

QuartersMap:
MAPPING LOAD
RowNo() as Month,
'Q' & Ceil (RowNo()/3) as Quarter
AUTOGENERATE (12);

Temp:
LOAD
min(Date(Floor([DateDispensed]))) as minDate,
max(Date(Floor([DateDispensed]))) as maxDate
Resident [Main Data];

LET varMinDate = Num(Peek('minDate', 0, 'Temp'));
LET varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD DISTINCT
$(varMinDate) + IterNo()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load *,
AutoNumber(Year & Quarter, 'QuarterID') as [QuarterID],
AutoNumber(Period, 'PeriodID') as [PeriodID]
;
LOAD DISTINCT
TempDate as [DateDispensed],
Year(TempDate) * 100 + Month(TempDate) as [Period],
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
YearToDate(TempDate)*-1 as CurYTDFlag,
YearToDate(TempDate, -1)*-1 as LastYTDFlag,
InYear(TempDate, MonthStart($(varMaxDate)),-1) as RC12,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
Week(Weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate asc;
DROP Table TempCalendar;

Filtered:
LOAD
Year as Last5Year
Resident [MasterCalendar] Where Year >= Year(Today()) - 4 and Year <= Year(Today());

//SortingOrderMonth_Temp:
//LOAD
//Month
//Resident MasterCalendar
//Order by Month asc;
//DROP Table SortingOrderMonth_Temp;

////--- Remove the temporary variables
LET varMinDate = Null();
LET varMaxDate = Null();

Re: Re: Re: Re: Mini Charts and Line Charts

danieloberbilli — Tue, 04 Nov 2014 00:11:20 GMT

Its already in the right sorting order in your case (order by TempDate asc).

So beside of sorting by numerical values you could also use the chart properties sorting order 'Load Order: Original'

Kind Regards

Daniel

Re: Re: Re: Re: Mini Charts and Line Charts

Anonymous — Tue, 04 Nov 2014 03:49:56 GMT

Many thanks, Daniel, most appreciated.

Regards.

Chris