Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How to set up correct sorting in bar chart

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

screenshot 3.png

Thank you!

11 Replies
mjm
Contributor III

Re: How to set up correct sorting in bar chart

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

Employee
Employee

Re: How to set up correct sorting in bar chart

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)

MVP
MVP

Re: How to set up correct sorting in bar chart

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.

Not applicable

Re: How to set up correct sorting in bar chart

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
Contributor III

Re: How to set up correct sorting in bar chart

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.
Not applicable

Re: How to set up correct sorting in bar chart

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
Contributor III

Re: How to set up correct sorting in bar chart

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.
Not applicable

Re: How to set up correct sorting in bar chart

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

raju_insights
Contributor III

Re: How to set up correct sorting in bar chart

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

Community Browser