Need Help??

[paulwalker]

Hi Community,

How to calculate Bucket based on date

(0-30 days, 30-60 days, 60-90 days and Above 90 days) but based on Date Field

I need only 0-30, 31-60, 61-90, Above 90......

suppose i have 2012, 2013, 2014 years data

if i am click 2012 i want to show this year data  (0-30, 31-60, 61-90, Above 90......)

same as 2013 and 2014

by default current year data..

Here 0-30 days means (suppose today 9/18/2014 = back 30 days means 8/20/2014)

see like this

9/18/2014

9/17/2014

9/16/2014

9/15/2014

9/14/2014

9/13/2014

9/12/2014

9/11/2014

9/10/2014

9/9/2014

9/8/2014

9/7/2014

9/6/2014

9/5/2014

9/4/2014

9/3/2014

9/2/2014

9/1/2014

8/31/2014

8/30/2014

8/29/2014

8/28/2014

8/27/2014

8/26/2014

8/25/2014

8/24/2014

8/23/2014

8/22/2014

8/21/2014

8/20/2014

Hope you Understand...same as 31-60, 61-90 and Above 90  (Above 90 means 91 days to 365 days)

please help me...

[maxgro]

use a calculated dimension with class function and the difference between today and date

=class(today() - Date,30)

[paulwalker]

I't ok,

But we are using CLASS function...

It showing like this...

But i don't need all 0-30, 31-60, 61-90 and Above 90(Above 90 means 91 to 365 days)

When i am open my application i want show Current...

and if i am select 2012 year, i want to show 2012 year data... based on selection..

Hope you understand..........

[paulwalker]

Please anyone can help me............

[#5]

Use the expression above in an if statement and define the dimension value with hard coded text:

E.g. if(date#(Date)-today() <=30, '0 to 30',

          if(date#(Date)-today()>30 and date#(Date)-today()<=60, '31 to 60',

               if(date#(Date)-today() >90 and date#(Date)-today() <='90', '61 to 90'

                    '>90'

          )))

then either....

          as Aging_Category, if in a script, or just use as an expression in a calculated dimension. You might not need to the date# bit, depends what type your data is.

[maleksafa]

check this link:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/07/14/buckets

[jagan]

Hi Paul,

Try like this in script

LOAD

*,

if((DateFieldName)-today() >90, 'Above 90',

     if((DateFieldName)-today() > 60, '61 to 90',

     if((DateFieldName)-today() > 30, '31 to 60',

     if((DateFieldName)-today() <=30, '0 to 30')))) AS Aging

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

[paulwalker]

Hi Watson,

Thanks for response..

I have created bucket but it showing Above 90,

But i have data 0-30, 31-60 and 61-90

it's not showing properly.....

PFA,

[paulwalker]

Hi Jagan,

Please check this application..

it showing wrong data..

Here 0-30 --> back 30 days

31-60--> back 31 day-60 days...

[#10]

Hello,

Please check attached. Not sure if you want it this way.