topic Re: Manuely sort over a calculated Dimensions in QlikView

Manuely sort over a calculated Dimensions

Wed, 25 Nov 2020 16:16:04 GMT

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.

Product	Time	Date
A	22:00	16.01.2017
B	23:00	16.01.2017
C	0:00	17.01.2017
D	4:00	17.01.2017

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

Product	Time	Date
C	0:00	17.01.2017
D	4:00	17.01.2017
A	22:00	17.01.2017
B	23:00	17.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.

Re: Manuely sort over a calculated Dimensions

hector_munoz — Tue, 17 Jan 2017 21:22:45 GMT

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,

Re: Manuely sort over a calculated Dimensions

eespiritu — Tue, 17 Jan 2017 21:33:10 GMT

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,

Re: Manuely sort over a calculated Dimensions

trdandamudi — Tue, 17 Jan 2017 21:37:09 GMT

Is this what you are looking for ?

Re: Manuely sort over a calculated Dimensions

Mon, 23 Jan 2017 08:36:59 GMT

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

...

Re: Manuely sort over a calculated Dimensions

Mon, 23 Jan 2017 08:44:25 GMT

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.

Re: Manuely sort over a calculated Dimensions

trdandamudi — Mon, 23 Jan 2017 12:47:34 GMT

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