topic Re: Sum Difference in QlikView

Sum Difference

Mon, 16 Mar 2015 17:44:01 GMT

Hi All

I am creating a report to calculate the number of people in a building per hour throughout the day.

The current Hour format is as shown below, I would like this to be as indicated in the second table.

Hour, Number_Of_People

2:00, 164

3:00, 178

4:,00, 192

Hour, Number_Of_People

1:00 - 2:00, 164

2:00 - 3:00, 178

3:00 - 4:,00, 192

how can I achieve this?

Re: Sum Difference

jfkinspari — Mon, 16 Mar 2015 17:54:11 GMT

I would make a mapping table, where you write the exact format of each time period.

This is an easy solution, if you only have a limited number of time periods, e.g. 24hours

Re: Sum Difference

maxgro — Mon, 16 Mar 2015 17:56:32 GMT

try this calculated dimension

=time(subfield(class(Hour,1/24), '<=', 1), 'hh:mm' ) & ' - ' & time(subfield(class(Hour,1/24), '<', 3), 'hh:mm' )

Re: Sum Difference

Gysbert_Wassenaar — Mon, 16 Mar 2015 17:58:03 GMT

Use a calculated dimension =dual( time(time#(Hour,'h:mm')-1/24,'h:mm') & ' - ' & time(time#(Hour,'h:mm'),'h:mm') ,Hour)

Or format the time in the script:

LOAD

dual( time(time#(Hour,'h:mm')-1/24,'h:mm') & ' - ' & time(time#(Hour,'h:mm'),'h:mm') ,Hour) as Hour,

Number_of_peope

INLINE [

Hour, Number_of_peope

1:00, 13

2:00, 24

3:00, 56

4:00, 64

5:00, 89

6:00, 107

7:00, 202

8:00, 255

9:00, 350

See attached qvw.

Re: Sum Difference

mukesh24 — Mon, 16 Mar 2015 18:02:15 GMT

hi tumelo,

u achieve these using Interval Match Function.Please check example

Regards,

Mukesh

Re: Sum Difference

Mon, 16 Mar 2015 18:13:29 GMT

Thanks a lot for assistance guys.

Is there a way to rename a calculated dimension so that it doesn't show the formular on from end?

Re: Sum Difference

Gysbert_Wassenaar — Mon, 16 Mar 2015 18:18:15 GMT

Enter a text in the Label setting.

Re: Sum Difference

Mon, 16 Mar 2015 18:25:47 GMT

Oh....how did I not see that?...anyway thanks so much man I appreciate it!