Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

vvvvvvizard
Contributor III

Sort on a drill down dimension - qlik sense

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

1 Solution

Accepted Solutions
Employee
Employee

Re: Sort on a drill down dimension - qlik sense

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;

Capture.PNG.png

Capture1.PNG.png

Capture2.PNG.png

2 Replies
Employee
Employee

Re: Sort on a drill down dimension - qlik sense

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;

Capture.PNG.png

Capture1.PNG.png

Capture2.PNG.png

martinien
New Contributor III

Re: Sort on a drill down dimension - qlik sense

Hi,

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