Creating Hour range Qlik Sense

[UserID2626]

Hi,

I have two columns Date and hour.

My requirement is to create a column called range with interval of two hours(highlighted in blue color)

Date	Hour	RANGE
01-Jan-20	0	1-Jan-20 (0 AM 02 AM)
01-Jan-20	3	1-Jan-20 (3 AM 05 AM)
01-Jan-20	5	1-Jan-20 (5 AM 07 AM)
01-Jan-20	21	1-Jan-20 (21 PM 23 PM)
01-Jan-20	23	1-Jan-20 (23 PM 01 AM)
01-Jan-20	16	1-Jan-20 (16 PM 18 PM)

Help me with script to create the range column

[Mauritz_SA]

Hi there

Try this (change the MappingTable according to your liking):

MappingTable:
Mapping
Load
*
INLINE [
Hour,RangeValue
0,(0 AM 2 AM)
1,(1 AM 3 AM)
2,(2 AM 4 AM)
3,(3 AM 5 AM)
4,(4 AM 6 AM)
5,(5 AM 7 AM)
6,(6 AM 8 AM)
7,(7 AM 9 AM)
8,(8 AM 10 AM)
9,(9 AM 11 AM)
10,(10AM 12 PM)
11,(11AM 13 PM)
12,(12 PM 14 PM)
13,(13 PM 15 PM)
14,(14 PM 16 PM)
15,(15 PM 17 PM)
16,(16 PM 18 PM)
17,(17 PM 19 PM)
18,(18 PM 20 PM)
19,(19 PM 21 PM)
20,(20 PM 22 PM)
21,(21 PM 23 PM)
22,(22 PM 0 AM)
23,(23 PM 1 AM)
];

LOAD Date, Hour, Date &' '& ApplyMap('MappingTable', Hour) AS RANGE INLINE [
Date,Hour
01-Jan-20,0
01-Jan-20,3
01-Jan-20,5
01-Jan-20,21
01-Jan-20,23
01-Jan-20,16
];

Regards,

Mauritz

[UserID2626]

Mauritz_SA,

The method you given was static.

If  clicking on hour 2 in filter range should be 2AM 4AM (Should sum the value of 2AM,3AM,4AM)

If clicking on hour 3 in filter range should be 3AM 5AM (Should sum the value of 5AM,3AM,4AM)

Please help me to achieve this with set expression based on selections

[Mauritz_SA]

Hi @UserID2626 

Sorry, I didn't understand the request to be that. Can you maybe give a sample table and the desired end result?

[UserID2626]

This will be my source table

Date	Hour	Amount
25-Jun-19	0	45
25-Jun-19	1	85
25-Jun-19	2	123
25-Jun-19	4	96
25-Jun-19	5	741
25-Jun-19	9	55

This will be my chart by default

Interval	Total Amount
25-Jun-2019 0AM 3AM	253	this is 12 AM to 3 AM
25-Jun-2019 3AM 6AM	837
25-06-2019 9AM 0PM	55

if i select 1 in filter,my result should be

Interval	Total Amount
25-Jun-2019 1AM 4AM	208	this is 1 AM to 4 AM
25-Jun-2019 4AM 7AM	837
25-06-2019 7AM 10AM	55

My source will get refresh for 3 hours. Based on Selection my chart should change, help me with set expression

Or 

i need a output in table without Date like below

Interval	Total Amount
1AM 4AM	208	this is 1 AM to 4 AM
4AM 7AM	837
7AM 10AM	55

[Mauritz_SA]

Hi @UserID2626 

Your situation seems quite complex and I'm not sure whether I'll be able to help, maybe someone else can assist you.

Sorry for not being of more help.

Mauritz