topic Re: Re: Problems with sorting on drill down dimension in App Development

Problems with sorting on drill down dimension

Tue, 30 Sep 2014 08:59:40 GMT

I have a line graph on a drill down dimension (month - day). The data shows up fine at first (when showing months) but when I drill down into a month (select a month and click confirm) the dates are not sorted properly but appears in what seem as random order. I have tried different sorting options in the chart configuration but without any luck.

Before selection:

After selecting a month:

Any help is appreciated!

/Viktor

Re: Problems with sorting on drill down dimension

Tue, 30 Sep 2014 14:32:08 GMT

Hi Viktor,

This is a known issue in our R&D department and people are working on that. Unfortunately, so far we only support sorting on the 'top layer' of a drill-down dimension. Let me think about it and see if we have any walk around of that for now.

Re: Problems with sorting on drill down dimension

rubenmarin — Tue, 30 Sep 2014 15:05:25 GMT

Hi Viktor, have you tried the 'Sort command...' button in the edit group window?:

Re: Problems with sorting on drill down dimension

Tue, 30 Sep 2014 15:08:25 GMT

Bohua,

Thanks for letting me know. I'll have to think about how to visualize longer time periods.

Ruben,

I'm using qlik sense so I don't have that option (I think).

Re: Problems with sorting on drill down dimension

rubenmarin — Tue, 30 Sep 2014 15:15:58 GMT

Ops, sorry, i din't see it was for Sense.

Re: Re: Problems with sorting on drill down dimension

JonnyPoole — Tue, 30 Sep 2014 15:39:05 GMT

Hi there - i think you can accomplish this using some data modelling techniques.

I just posted the technique in this other thread... respond in your current thread if you can't get it to work. I tried many ways and ended up with this . Also attached is a QVF sample.

Re: Sort on a drill down dimension - qlik sense

Re: Problems with sorting on drill down dimension

Wed, 01 Oct 2014 12:18:45 GMT

Thank you, Jonathan. I will have a look at your solution and return to this thread with my findings

Re: Problems with sorting on drill down dimension

Tue, 28 Oct 2014 10:13:51 GMT

Hi.

I have the same problem, but im fetching my info from SQL server.

Could you help me fill in the formula? Can't seem to get it working in any way..

_____________________________

LIB CONNECT TO 'XX.XX.XX.X';

LOAD "No_" as Projekti,

"Creation Date" as PVM,

Day("Creation Date")*-1 as OrderDateFact,

Year("Creation Date")*-1 as OrderYearFact,

Month("Creation Date")*-1 as OrderMonthFact;

SQL SELECT "No_",

"Creation Date"

FROM "XXXXXXXXXXXX";

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: Problems with sorting on drill down dimension

tanelry — Tue, 28 Oct 2014 13:05:07 GMT

I think you only need to have PVM field in the OrdersHeader table.

Years:

load distinct

PVM,

Year(PVM) as OrderYear

resident OrdersHeader

order by PVM desc;

Months:

load distinct

PVM,

Month(PVM) as OrderMonth

resident OrdersHeader

order by OrderMonthFact desc;

etc...

Re: Problems with sorting on drill down dimension

JonnyPoole — Tue, 28 Oct 2014 14:12:00 GMT

Hi - the Years , Months, and Dates tables are loaded from the data that comes back from the first table.

In my script i named the first table OrdersHeader , but i don't see that in your example.

In the Load Editor, please add the following line in front of the first load as follows to give the table the correct name. That way when the other tables load from a table that is already 'resident ' , (ie: resident OrderHeader) it should work.

Then in you chart, don't use the 'fact' fields, you should see OrderDate,OrderYear, and OrderMonth available to build the drill down. Recreate the dimension from scratch as well as the chart.

let me know what happens !

OrdersHeader:

LOAD "No_" as Projekti,

"Creation Date" as PVM,

Day("Creation Date")*-1 as OrderDateFact,

Year("Creation Date")*-1 as OrderYearFact,

Month("Creation Date")*-1 as OrderMonthFact;

SQL SELECT "No_",

"Creation Date"

FROM "XXXXXXXXXXXX";

Re: Problems with sorting on drill down dimension

Fri, 31 Oct 2014 06:19:37 GMT

Thanks,

I used another tip of yours. (Qlik Sense Desktop - how can I filer by date? | Qlik Community)

Is there a function to filter out only one year in the data load editor, like if you have dates from 2011 to 2014 and want only to get the 2014 dates and related content.

LOAD "No_" as Projekti,

Year2014("Creation Date") as Year-2014

or something similar..

Re: Problems with sorting on drill down dimension

JonnyPoole — Fri, 31 Oct 2014 17:42:16 GMT

Hi max - for sure - can you put this in a new thread ? it makes it easy for other users to find solutions.