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]

Sorry to bother you, but this above expression is bring by default 10 days , but when i select a date at filter then it is bringing only one selected date and since by deafult for first 10 days we added bucket if we select any other date bucket is null and when we select one date we are not getting previous 10 dates at report, we are getting only one date which was selected at filter.

We should get 10 previous dates dynamically from selected date and that 10 dates should have bucketting,

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

[beskqlik]

HI

Thank you for all your support

need one help, i have date column as data_date, when i used that column name i am not getting any data.Do i need to make any changes to the expression.

=if( max({$<DATA_DATE={"=$(=max(Date))"}>} total DATA_DATE)-only({$<DATA_DATE={"<=$(=max(Date))"}>} DATA_DATE)<3,
'1-3 days',
if( max({$<DATA_DATE={"=$(=max(Date))"}>} total DATA_DATE)-only({$<DATA_DATE={"<=$(=max(Date))"}>} DATA_DATE)<6,
'4-6 days',

if( max({$<DATA_DATE={"=$(=max(Date))"}>} total DATA_DATE)-only({$<DATA_DATE={"<=$(=max(Date))"}>} DATA_DATE)<10,
'7-10 days',
'greater than 90'))
)

[beskqlik]

Also when  i am trying to use this expression as dimension, it is giveing error as not valid dimension

[beskqlik]

need one more help,I also need to create a qlik sense report with count of units based on buckecting and another dimension columnA

                                                                    1-3 days                             4-6 days                     7-10 days

ColumnA                       

A1                                                                    count(units)1                3                                             6

A2                                                                              6                                   7                                     7

[beskqlik]

when i am using below expression as column dimension, i am getting error as invalid dimension. can  you please help

=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','greater than 11 days'
)
)
)

[Vegar]

The max(Date) should also be converted with the new field name.

=if( max({$} total
DATA_DATE)-only({$} DATA_DATE)<3,
'1-3 days',
if( max({$} total
DATA_DATE)-only({$} DATA_DATE)<6,
'4-6 days',

if( max({$} total
DATA_DATE)-only({$} DATA_DATE)<10,
'7-10 days',
'greater than 90'))
)

[beskqlik]

I tried giving max(data_date) in one column and using that column name , it is giving error.

Can you please suggest how i can create a field for max(data_date) and use this expression. Can you please help on this

[beskqlik]

can you provide my solution, waiting for your help. Can you please support on this for me

[Vegar]

Sorry @beskqlik  I do not have  an answer for you. Maybe someone else in this forum does?  Maybe @dplr-rn  or @sunny_talwar can help you with a calculated dimension version of this expression. 

[beskqlik]

Thank you for all your support. Yeah will try to get solution from some other.

But you mentioned as to get max(data_date) to avoid invalid dimension error. I tried but it did not worked. I might be making mistake. If you have done that, can you please share qvf.