2 Replies Latest reply: Jan 6, 2015 4:38 AM by Martin Sorel RSS

    Sort on a drill down dimension - qlik sense

    yusuuf rawat

      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
          Jonathan Poole

          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