days bucketing

[beskqlik]

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.

[beskqlik]

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]

can any one please suggest

[beskqlik]

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]

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]

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]

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

[Vegar]

Please see attached qvf file

[beskqlik]

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 · Accepted solution]

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