
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- « Previous Replies
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=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()
)
)
)
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
can any one please suggest

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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') )
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
inline load is giving error as date is not valid to load inline
Expression is not working, it is going to last else part 😞

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Expression is giving error as invalid dimension. Can you please suggest as where i am doing wrong

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=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()
)
)
)
Qlik Community MVP

- « Previous Replies
- Next Replies »