Solved: Re: Creating buckets in Qliksense - Qlik Community

Anonymous · ‎2017-02-02

Hi,

I have the following data.

Interval
0:00-0.30
0:30-0:59
1:00-1:30
1:30-1:59
2:00-2:30
2:30-2:59

and so on up to 23:30-23:59. I want to create buckets as Morning,Afternoon and Evening for these times. Can someone help me with the same.

Anil_Babu_Samineni · ‎2017-02-05

For me this is working

I've used sureshqv Expression and added Single Colon

If([30MinInterval]> '0:00' and [30MinInterval]<= '4:59', 'Afernoon',

If([30MinInterval]> '4:59' and [30MinInterval]<= '7:59', 'Evening',

If([30MinInterval]> '7:59', 'Night','Morning')))

OR, This for full Time slots

If([30MinInterval]> '0:00' and [30MinInterval]<= '4:59', Dual('Afernoon',1),

If([30MinInterval]> '4:59' and [30MinInterval]<= '7:59', Dual('Evening',2),

If([30MinInterval]> '7:59', and [30MinInterval]<= '17:59', Dual('Night',3),

If([30MinInterval]> '17:59', and [30MinInterval]<= '23:59', Dual('Morning',4))))

OR, This for Full timestamp using Default function

If([30MinInterval] > Time#('00:00', 'hh:mm') and [30MinInterval] <= Time#('04:59','hh:mm'), Dual('Afternoon', 1),

If([30MinInterval] > Time#('04:59', 'hh:mm') and [30MinInterval] <= Time#('07:59','hh:mm'), Dual('Evening', 1),

If([30MinInterval] > Time#('07:59', 'hh:mm') and [30MinInterval] <= Time#('17:59','hh:mm'), Dual('Night', 1),

If([30MinInterval] > Time#('17:59', 'hh:mm') and [30MinInterval] <= Time#('23:59','hh:mm'), Dual('Morning', 1),

))))

Morning won't effect due to you don't have data on your file for second slot

HTH,

Best

ANIL

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

Anil_Babu_Samineni · ‎2017-02-03

As per your logic,

What times and hours you want to see

Morning, Afternoon, Evening

00:00 AM - 12:00 PM, 12:00 PM - 06:00 PM, 06:00 PM - 00:00 AM --- Is this you are expecting to see?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Chanty4u · ‎2017-02-03

you can try something like below

Edit with your respective fields.

Load

If(Interval<= 00:10:00, 'Under 10 min'

If(Interval>=00:10:00 and VänteTid <= 00:15:00, '10-15 Min',

If(Interval> 00:15:00 and VänteTid <= 00:30:00, '15-30 Min',

If(Interval> 00:30:00 and VänteTid <= 00:45:00, '30-45 Min',

If(Interval> 00:45:00 and VänteTid <= 00:60:00, '45-60 Min'',

If(Interval> 00:60:00, 'Över 60 Min')))) as TidInterval

jonathandienst · ‎2017-02-03

Adjust the time (and fieldnames too) to your requirements:

If(Interval <= Time#('12:00:00', 'hh:mm:ss'), Dual('Morning', 1),

If(Interval <= Time#('18:00:00', 'hh:mm:ss'), Dual('Afternoon', 2), Dual('Evening', 3))

) as TimeBucket

Duals to ensure that the buckets sort in a logical fashion.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Anonymous · ‎2017-02-03

It should be in this fashion.

0:00-4:59 - Afternoon

5:00-7:59 - Evening

8:00-17:59 - Night

18:00-23:59 - Morning

Chanty4u · ‎2017-02-03

try below

If(Interval> 0:00 and Interval<= 4:59, 'Afernoon',

If(Interval> 4:59 and Interval<= 7:59, 'Evening',

If(Interval> 7:59, 'Night','Morning')))) as TimeInterval

Anil_Babu_Samineni · ‎2017-02-03

May be try something like below

If(Interval> (SubField(Interval,'-',-1) = '00:00') and Interval <= (SubField(Interval,'-',1) = '04:59'), Dual('Afernoon',1),

If(Interval> (SubField(Interval,'-',-1) = '04:59') and Interval<= (SubField(Interval,'-',-1) = '07:59'), Dual('Evening',2),

If(Interval> (SubField(Interval,'-',-1) = '07:59'), Dual('Night',3),Dual('Morning',4))))

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2017-02-03

Only night is appearing. Could you pls elaborate.

Anil_Babu_Samineni · ‎2017-02-03

Would you provide qvf to work on it with a gun shot answer

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2017-02-05

I have attached sample data.