Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

aishwarya17
New Contributor II

Creating buckets in Qliksense

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.

1 Solution

Accepted Solutions

Re: Creating buckets in Qliksense

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

10 Replies

Re: Creating buckets in Qliksense

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?

sureshqv
Esteemed Contributor III

Re: Creating buckets in Qliksense

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

MVP
MVP

Re: Creating buckets in Qliksense

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.

aishwarya17
New Contributor II

Re: Creating buckets in Qliksense

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

sureshqv
Esteemed Contributor III

Re: Creating buckets in Qliksense

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

Re: Creating buckets in Qliksense

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))))

aishwarya17
New Contributor II

Re: Creating buckets in Qliksense

timebuk.png

Only night is appearing. Could you pls elaborate.

Re: Creating buckets in Qliksense

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

aishwarya17
New Contributor II

Re: Creating buckets in Qliksense

I have attached sample data.