topic Re: Sort by date and then by time in QlikView

Sort by date and then by time

Anonymous — Wed, 08 Nov 2017 15:49:20 GMT

Hi everyone,

I am struggling to sort my data in a graph.

What I need:

1. Sort the data by date:

11.11.2017

12.11.2017

13.11.2017

2. In the same day, sort the data by time:

11.11.2017

6:12:10
7:14:45
13:01:01

12.11.2017

5:02:02
17:02:00

13.11.2017

etc....

Could you please support on this?

thanks

Mat

Re: Sort by date and then by time

YoussefBelloum — Wed, 08 Nov 2017 16:41:03 GMT

Hi,

can you attach a sample data ? to see the structure of the table you have

Re: Sort by date and then by time

el_aprendiz111 — Wed, 08 Nov 2017 16:55:46 GMT

Hi Clement

Can you share a sample with an example ?

1-option

Re: Sort by date and then by time

MarcoWedel — Wed, 08 Nov 2017 23:47:38 GMT

Using timestamps (i.e. date and time part) this should come naturally.

Re: Sort by date and then by time

Anonymous — Thu, 09 Nov 2017 10:43:55 GMT

Hi!

please find attached the excel file with data

hope it helps

I would like to be able to sort it also correctly in the graph

Re: Sort by date and then by time

YoussefBelloum — Thu, 09 Nov 2017 10:49:21 GMT

there is no attached files

Re: Sort by date and then by time

Anonymous — Thu, 09 Nov 2017 11:07:35 GMT

Re: Sort by date and then by time

Anonymous — Thu, 09 Nov 2017 11:24:22 GMT

I cannot find the way to attach the excel file

Re: Sort by date and then by time

shraddha_g — Thu, 09 Nov 2017 11:27:20 GMT

Refer Uploading a Sample

Re: Sort by date and then by time

Anonymous — Thu, 09 Nov 2017 11:55:22 GMT

thanks

Re: Sort by date and then by time

Anonymous — Thu, 09 Nov 2017 19:38:28 GMT

Do you need any other information? Script ? Layout of multitable and graph?

Re: Sort by date and then by time

MarcoWedel — Thu, 09 Nov 2017 21:58:16 GMT

Where does the Format dimension come from?

Re: Sort by date and then by time

Anonymous — Fri, 10 Nov 2017 15:20:39 GMT

The format comes from a second sheet, a simple link between material number (key) and format (see file attached).

The idea is quite simple:

1. Sort the material number by date (activity start)

2. For each date, sort the material by time (time start) which consist of starting time of machines (ressources)

3. once the sorting is correct, the graph with colors link the material number (key) with the format. For each format you have a different colors, it gives a good visual overview of material changes on the production line.

But to see the right order, I need to sort it by date and time...

thanks for the support

Mat

Re: Sort by date and then by time

MarcoWedel — Sun, 12 Nov 2017 00:28:25 GMT

Hi,

maybe one solution might be:

or using a horizontal bar chart:

mapFormat:
Mapping
LOAD Material,
    Text(Format)
FROM [https://community.qlik.com/servlet/JiveServlet/download/1379667-303338/Formate.xlsx] (ooxml, embedded labels, table is Sheet2);

tabTemp:
LOAD *,
    Timestamp([Activity Start]+[Time Start]) as ActStartDateTime;
LOAD Date#([Activity Start],'DD.MM.YYYY') as [Activity Start],
    [Time Start],
    Text(ApplyMap('mapFormat',Material)) as Format
FROM [https://community.qlik.com/servlet/JiveServlet/download/1378480-303098/example%20qlikview.xlsx] (ooxml, embedded labels, table is [Plan KW+3])
Where [Activity Start];

tabTemp2:
LOAD *,
    RowNo() as FormatSeq
Resident tabTemp
Where Format<>Previous(Format)
Order By ActStartDateTime;

DROP Table tabTemp;

tabResult:
LOAD *,
    DayName(ActStart) as ActDate,
    Interval(ActEnd-ActStart) as Duration;
LOAD *,
    If(IterNo()=1      , ActStartDateTime, DayStart(ActStartDateTime+IterNo()-1)) as ActStart,
    If(IterNo()=NoOfDays, ActEndDateTime  , DayEnd(  ActStartDateTime+IterNo()-1)) as ActEnd
While IterNo()<=NoOfDays;
LOAD *,
    Previous(ActStartDateTime) as ActEndDateTime,
    Floor(Previous(ActStartDateTime))-Floor(ActStartDateTime)+1 as NoOfDays
Resident tabTemp2
Order By FormatSeq desc;

DROP Table tabTemp2;

hope this helps

regards

Marco

Re: Sort by date and then by time

Monicalingan — Sun, 12 Nov 2017 09:59:51 GMT

Hi Matt,

I have changed the format of [Activity Start] field to help in sorting of date and time as expected. Please see script attached.

After reloading make sure you sort on [Activity Start] numeric values ascending then in the [Time Start] field numeric values ascending.

Sorting new vs old activity start.png compares how it sorted before and after formatting. I have also tried to recreate the Mekko chart and filtered on the shown formats.

Hope it helps.

Thanks,

Monica

Re: Sort by date and then by time

Anonymous — Mon, 13 Nov 2017 10:22:29 GMT

thanks for your help, unfortunately changing the format of dates doesn't help much.

take this example:

graph overview: If correct, the "red part" should be under the "blue" part on date 14/11/2017

- morning top of the graph

- evening down

data:

Format:

Re: Sort by date and then by time

Anonymous — Mon, 13 Nov 2017 10:23:15 GMT

Is it maybe simply the axis of the graph that should be managed??? with time???

Re: Sort by date and then by time

Anonymous — Mon, 13 Nov 2017 10:27:40 GMT

or maybe simply the graph properties?

Re: Sort by date and then by time

Anonymous — Mon, 13 Nov 2017 10:31:56 GMT

Maybe I could test that: Do you know how to change the axis on the left of the graph so that the time goes from 00:00:00 on the top to 24:00:00 on the bottom??

the contrary as on the image below