Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Manuely sort over a calculated Dimensions

Good evening!

We have a data set from some productionhouse. From their point of view the start of their work is around 22:00 o'clock and they end around 17:00 o'clock. So they are starting for example at 22:00 16.01.2017 and had their end of work at 17:00 17.01.2017. Each product that is manufactured inbetween gets a timestamp (hh:mm) and a datestamp. They are both in different columns. The twist lies in the datestamp, because even if they are starting on 16.01.2017  at 22:00 the datestamp for this data set will automatically make the 17.01.2017 22:00.

To describe it better I try to show it in two tables.

In the first table i show you the actually time and date the normaly would be shown in a Table.

ProductTimeDate
A22:0016.01.2017
B23:0016.01.2017
C0:0017.01.2017
D4:0017.01.2017

And now how the data is when I look at the table

Product
TimeDate
C0:0017.01.2017
D4:0017.01.2017
A22:0017.01.2017
B23:0017.01.2017

To show ho many products are created in an hour I created a bar chart with the Dimension '=hour(time)' on the Y-Achse and a formula that just counting the Products. So far it is ok and the values are right. The Problem comes with the follow requirement. When we show the bar chart, the bars are orderer like in table 2. On the first place 0.00 hr and how many products has been manufactured in this hour. The last bar is the 23hr bar. The customer wants have a change in the rankings or in the sort of this bar, that the hour will on the y-achse will be wortet like in table 1 of my example.

I tried already in the sort expression the match() funktion and also researched a little bit about the dual() funktion, but couldn't find a clue about a solution. Could someone please guide me in the right direction. If it doesn't work with the sort expression, could the solution lie within the backend...maybe in creating a new column, that will change the date befor 00:00 in the right date. But sadly I don't have to much experience at the backend.

I would appriciate any Help that I could get.

Many thanks in advance.

6 Replies
hector_munoz
Specialist
Specialist

Hi Daniel,

Maybe you could fix it ordering the hours by an expression in the order tab. The expression colud be something like this: Avg(If(hour(time) = 22 OR hour(time) = 23, 24 - hour(time), hour(time))) and you had to order it ascending.

The value of each hour wolud be:

00 - 00

01 - 01

02 - 02

03 - 03

04 - 04

05 - 05

06 - 06

07 - 07

08 - 08

09 - 09

10 - 10

11 - 11

12 - 12

13 - 13

14 - 14

15 - 15

16 - 16

17 - 17

22 - -02

23 - -01


, and values 22 and 23 would appear in the first places, followed by 00, 01, ..., 17.


Please, colud you try it?


Regards,

H

eespiritu
Creator
Creator

Hi Daniel,

You can define the sort by Load Order (Original) in the chart properties, but that sorting you need to create it in your script, adding an extra field to identify the first, second, third... final ID for your dimension.

Table:

Load

     if(Time = '22:00', 1, if(Time = '23:00', 2, if(Time = '0:00', 3, if(Time = '4:00', 4)))) as IDSort,

     *

Resident YourTable;

Maybe the match() function is not working because the values of this dimension is a timestamp, I'm not sure.

Regards,

trdandamudi
Master II
Master II

Is this what you are looking for ?

Sort_Order_17_Jan_2017.jpg

Not applicable
Author

Thank you very mutch for your quick response. I used your sort expression, but did change a small thing.

Avg(If(hour(time) = 22 OR hour(time) = 23, hour(time)- 24, hour(time)))

Without this change the order was a little bit off

00

01

22

23

02

03

...

Not applicable
Author

Thank you Enrique and Thirumala Dandamudi,

I tried your solution in the backend part and it also did work and because of that I learnd a little bit more about the backend.

trdandamudi
Master II
Master II

You are welcome...If you think you got the answer then please close the thread appropriately....