Sort on a drill down dimension - qlik sense

vvvvvvizard — Thu, 25 Sep 2014 11:10:05 GMT

Good day , Ive created a drill down dimension which is used in a bar chart .

1. =month(the_date) (which shows april , may , june ... ect)

2. ='Week'&Ceil(Day(the_date)/7) ( which shows week 1 , week 2 , week 3... )

3. =The_date ( Which shows 01/04/2014 , 02/04/2014 .... ect )

How do i sort the number 2 and 3 in order . It must show week 1 , week 2 for dimension 2 and sort the dates in order for dimension 3. ?

Re: Sort on a drill down dimension - qlik sense

JonnyPoole — Tue, 30 Sep 2014 15:35:50 GMT

Hi , I've come up with a way to do this using data model techniques. In this example (similar but not same as yours) i have a drill from Year to Month (MMM) to Date

Need to create a table for each level in the drill down and use ORDER BY DESC to filter the negative values of Year,Month,Date to get the ascending order you want in the chart.

In the sample below, my drill down dimension is made up of OrderYear,OrderMonth,OrderDate. The 'fact' keys are just for joining and sorting . In your case you could add a weeks table. Hope this helps. Let me know if you can't get it to work.

OrdersHeader:

LOAD

OrderDate*-1 as OrderDateFact,

Year(OrderDate)*-1 as OrderYearFact,

Month(OrderDate)*-1 as OrderMonthFact,

CustomerID,

EmployeeID,

Freight,

OrderID,

ShipperID

FROM [lib://Individual XLS/OrdersHeader.xlsx]

(ooxml, embedded labels, table is OrdersHeader);

Years:

load distinct

OrderYearFact,

fabs(OrderYearFact) as OrderYear

resident OrdersHeader

order by OrderYearFact desc;

Months:

load distinct

OrderMonthFact,

left(monthName(date(fabs(OrderDateFact),3)),3) as OrderMonth

resident OrdersHeader

order by OrderMonthFact desc;

Dates:

load distinct

OrderDateFact,

date(fabs(OrderDateFact)) as OrderDate

resident OrdersHeader

order by OrderDateFact desc;

Re: Sort on a drill down dimension - qlik sense

martinien — Tue, 06 Jan 2015 09:38:42 GMT

Hi,

I had the same problem,thanks for this, worked fine for me!

topic Re: Sort on a drill down dimension - qlik sense in App Development

Sort on a drill down dimension - qlik sense

Re: Sort on a drill down dimension - qlik sense

Re: Sort on a drill down dimension - qlik sense