Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
beskqlik
Contributor II
Contributor II

days bucketing

I have one excel file and based on this file data i need to create days bucket

i have a column date1 where using this date1 column , i need to create 30,60,90 days bucket. When i select one date at filter, based on that ,from that particular date, i need to get dates and related buckets.

example, if select jan 1 2019, then i should get dec 1 2018 to 31 dec 2018 dates  and other bucket as 30 days for this related dates

But when i select date1 column at qliksense report, i am getting data but when i try to get as date1-1the no data displayed for me. Even when tried as date(date1,'MM/DD/YYYY') then also no data.

When  i try today() at report, i got current date but when try to get previous date as today()-1, then also i dont get any data.

Can any one please suggest as how can i move forward on this.

1 Solution

Accepted Solutions
Vegar
MVP
MVP

Try this expression:

=if( max({$} total
Date)-only({$} Date)<3,
'1-3 days',
if( max({$} total
Date)-only({$} Date)<6,
'4-6 days',
if( max({$} total
Date)-only({$} Date)<10,
'greater than 6 days',null()
)
)
)

View solution in original post

22 Replies
beskqlik
Contributor II
Contributor II
Author

HI

 

I am able to get bucketing of 30,60,90 days using difference between today and datecolumn i have. BUt i need to get 30,60,90 days back to the date i select at filter pane.Can any please suggest over here

beskqlik
Contributor II
Contributor II
Author

can any one please suggest

beskqlik
Contributor II
Contributor II
Author

Please find my requirment indetail with sample

Have  date column as below

date
3/20/2019
3/21/2019
3/22/2019
3/23/2019
3/24/2019
3/25/2019
3/26/2019
3/27/2019
3/28/2019
3/29/2019
3/30/2019
3/31/2019
4/1/2019
4/2/2019
4/3/2019
4/4/2019
4/5/2019
4/6/2019
4/7/2019

Need result as below

At Filter Panel selected date 4/2/2019

We should get last 10 days from selected dates as below                   Need bucket as below
3/24/2019                                                                                                                 greater than 6 days
3/25/2019                                                                                                                  greater than 6 days
3/26/2019                                                                                                                 greater than 6 days
3/27/2019                                                                                                                 greater than 6 days
3/28/2019                                                                                                                  4-6 days
3/29/2019                                                                                                                  4-6 days
3/30/2019                                                                                                                    4-6 days
3/31/2019                                                                                                                   1-3 days
4/1/2019                                                                                                                     1-3 days
4/2/2019                                                                                                                       1-3 days

Vegar
MVP
MVP

Take a look at this script and the expression below, it might be of help.

LOAD date#(date,'M/D/YYYY') as Date inline [
date
3/20/2019
3/21/2019
3/22/2019
3/23/2019
3/24/2019
3/25/2019
3/26/2019
3/27/2019
3/28/2019
3/29/2019
3/30/2019
3/31/2019
4/1/2019
4/2/2019
4/3/2019
4/4/2019
4/5/2019
4/6/2019
4/7/2019];

Expression

=if( max({$<Date={"=$(=max(Date))"}>} total Date)-only({$<Date={"<=$(=max(Date))"}>} Date)<3, 
'1-3 days', if( max({$<Date={"=$(=max(Date))"}>} total Date)-only({$<Date={"<=$(=max(Date))"}>} Date)<6,
'4-6 days','Greater than 6 days') )
beskqlik
Contributor II
Contributor II
Author

inline load is giving error as date is not valid to load inline

 

Expression is not working, it is going to last else part 😞

beskqlik
Contributor II
Contributor II
Author

Expression is giving error as invalid dimension. Can you please suggest as where i am doing wrong

Vegar
MVP
MVP

Please see attached qvf file

image.png

beskqlik
Contributor II
Contributor II
Author

Thank you for your help. It is working file. But when i am selecting one date at filter pane, i should get only 10 previous days date and related bucktes based on that.As below. Can you please help me on this too.

At Filter Panel selected date 4/2/2019

We should get last 10 days from selected dates as below Need bucket as below
3/24/2019 greater than 6 days
3/25/2019 greater than 6 days
3/26/2019 greater than 6 days
3/27/2019 greater than 6 days
3/28/2019 4-6 days
3/29/2019 4-6 days
3/30/2019 4-6 days
3/31/2019 1-3 days
4/1/2019 1-3 days
4/2/2019 1-3 days

 

Vegar
MVP
MVP

Try this expression:

=if( max({$} total
Date)-only({$} Date)<3,
'1-3 days',
if( max({$} total
Date)-only({$} Date)<6,
'4-6 days',
if( max({$} total
Date)-only({$} Date)<10,
'greater than 6 days',null()
)
)
)