Sum Difference

[#1]

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?

[jfkinspari]

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

[maxgro]

try this calculated dimension       

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

[Gysbert_Wassenaar · Accepted solution]

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.

[mukesh24]

hi tumelo,

u achieve these using Interval Match Function.Please check example

Regards,

Mukesh

[#6]

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?

[Gysbert_Wassenaar]

Enter a text in the Label setting.

[#8]

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