Mini Charts and Line Charts

[Anonymous]

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

[danieloberbilli]

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.

[kuba_michalik]

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.

[Anonymous]

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

[Anonymous]

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();
 

[danieloberbilli]

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

[Anonymous]

Many thanks, Daniel, most appreciated.

Regards.

Chris