Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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)
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.
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;
Make sure that you are using the date field from the master calendar in the 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;
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;
Rangaraju, I've included this field in MasterCal script but I'm still facing the same problem
What are the Dimensions you are using in the bar chart.