Sort on a drill down dimension - qlik sense

[vvvvvvizard]

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.  ?

[JonnyPoole · Accepted solution]

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;

[martinien]

Hi,

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