How to set up correct sorting in bar chart

[#1]

Hi all, I have a problem when creating a bar chart. I have a dimention - date  and measure - some expression.

But I can't make this chart showing all dates in order

I have a master calendar in my load script.

Can you please help me with that?

Sorting options in chart properties are not giving anything

Thank you!

[mjm]

Hello Evgeniy,

If you look at the sorting section of the properties of the bar chart, I would imagine this would be set to auto by default. If you try and switch this off and choose sort by expression, you should be able to use your date field name within the expression and use that as the sort order. Depending on your date format you may need to format this using the date() function within the expression. You should then also be able to either sort ascending or descending.

I hope this helps. If this does help, please mark this as helpful and correct.

Thanks,

Mark James

Qlik

[JonnyPoole]

select the chart and then on the right , expand sort and turn off 'automatic'.

choose sort by expression

for the expression try using date#() to interpret your dates to ensure qlik reads them as bonafide dates

Here is a sample (if your date field was called [DateField])

Date#(  [DateFIeld],'DD.MM.YY)

[jagan]

Hi,

The best solution for this is to convert your date to date format like below

LOAD

*,

Date(Date#(  [DateFIeld],'DD.MM.YY'), 'DD.MM.YY') AS Date_Formatted

FROM Datasource;

Now in chart use the Date_Formatted as dimension and select Asending/Descending in Sort tab.

Regards,

Jagan.

[#5]

Hi Jonathan, when I'm pasting your expression into expression editor it shows an error.

In load script I'm using this expression:

LOAD

Date(Floor(created_at)) as Date_created;

[raju_insights]

Make sure that you are using the date field from the master calendar in the bar chart.

• If 'date' is the key field which connects Master Calendar and other tables, then in master calendar create another field by duplicating 'date' field.
• Rename it as 'date_main'.
• Use 'date_main' field in all charts as Date dimension.

[#7]

Hi Rangaraju,

Here's my mastercal script. As connection field I use Date_created, could you please assist what should I change?

Thank you!

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

min(Date_created) as minDate, 

max(Date_created) as maxDate 

Resident Scripts; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

$(varMinDate) + Iterno()-1 As Num, 

Date($(varMinDate) + IterNo() - 1) as TempDate 

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

  TempDate AS Date_created, 

  week(TempDate) As Week, 

  Year(TempDate) As Year, 

  Month(TempDate) As Month, 

  Day(TempDate) As Day, 

  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; 

[raju_insights]

MasterCalendar:

Load

  TempDate AS Date_created,

TempDate AS Date_Main,

  week(TempDate) As Week,

  Year(TempDate) As Year,

  Month(TempDate) As Month,

  Day(TempDate) As Day,

  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;

• Include the above field mentioned in bold.
• Use Date_Main as Dimension instead of 'date' in the bar chart.

[#9]

Rangaraju, I've included this field in MasterCal script but I'm still facing the same problem

[raju_insights]

What are the Dimensions you are using in the bar chart.