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...

[paulwalker]

HI Amit,

This is Perfect,....

if ( (Today() - Date) >= 90, 'Over 90',

        if ((Today() - Date) >= 60, 'Over 60',

             if ((Today() - Date) >= 30, 'Over 30' , 'Above 90'))) as Bucket;

i need like this.... please try once

why it's showing wrong.....

[sundarakumar]

I dont think u should be doing this on the back end. bec u want the last 90 days details with respect to the user selection. When u write it in backend script it will refer only today's date to set bucket which would be wrong in ur case. so write the same in calculated dimension.

Sorry i have personal edition now, could not see what u have posted as example.

-Sundar

[#4]

Hi Paul,

Why do you want to show it in If condition ? Is there any issue with the solution I have given you ?

Thanks

Amit

[jagan]

Hi Paul,

Try this

Bucket:

Load *, if(today() - (Date) >90, 'Above 90',

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

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

     if(today() - (Date) <=30, '0 to 30')))) as Bucket;

LOAD Date,

  Year(Date) as Year,

  Month(Date) as Month,

     Sales

FROM

(ooxml, embedded labels, table is Sheet1);

Regards,

Jagan.

[Anonymous]

Hi Paul,

See the attached qvw .

Hope this would be helpful for you.

Regards

Manoj

[paulwalker]

Hi All,

Above 90 means--->  not current year

suppose i have 2014 data.. here  above 90 means --> i want to show all the data 2013 and so on..

only current data to display 0-30, 30-60, 60-90 and Above90

if i am click 2013 year it should be ABOVE 90

2012 also........

[#8]

Hi Paul,

Try this

Bucket:

Load *, if(today() - (Date) >90, 'Above 90',

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

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

     if(today() - (Date) <=30 and  if(today() - (Date) =<0, '0 to 30')))) as Bucket;

LOAD Date,

  Year(Date) as Year,

  Month(Date) as Month,

     Sales

FROM

(ooxml, embedded labels, table is Sheet1);

[qv_testing]

Hi Amit,

This is very helpful to me..

i need one help...

i have same requirement...but Above90

Here Above90 means not only current year..

Suppose i have 2014 year data.... it should be all (0-30, 30-60, 60-90 and Above90)

if i am clock 2013 it should be only Above90.....

0-30--> current date-last 30 days

30-60----> 31 to 60 days

60-90-->  61 to 90 days

Above90-->  91 days to.. last record...

Above90 not only current year data.. suppose i have 2009, 2010,2011,2012,2013,2014 years data..

0-30-->2014 data

30-60--> 2014 data

60-90--->2014 data

Above90--> above 90 days all years data

if i am click any 2009, 2010,2011,2012,2013 year it should be... Above90

please anyone can help me...........

It's very urgent...............................

Thanks in Advance............

[#10]

Hello,

You can try following.

Replace the Load statement in my above example with

Directory;

LOAD Date,

     Sales,

     Year(Date) as Year,

     num(Today()-MakeDate(Year(Date),Month(Date),Day(Date))) as Days

FROM

[New Microsoft Office Excel Worksheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

and Interval Match should be

BucketInterval:

LOAD * INLINE [

    MinBucket ,MaxBucket, BucketCategory

    0, 30, '0-30 days'

    31, 60,'31-60 days'

    61, 90,'61-90 days'

    91, max(Days),'Above 90 days'

];

Thanks

Amit Prabhudesai

[qv_testing]

Thank You very much Amit.....................