Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
how to create buckets month wise i want to see current month to previous three months one bucket and current month to previous six months second bucket ,Please find the attached excel.
could u please help on this.
Advance Thanks
Munna
Temp:
Load
*,
Interval(Today()-Date,'d') as NumberofDays;
Load
Date(Date#(Year,'DD/MM/YYYY')) as Date,
sales
Inline
[
Year, sales
01/01/2014, 10
01/02/2014, 20
01/03/2014, 30
01/04/2014, 40
01/05/2014, 50
01/06/2014, 60
01/07/2014, 70
01/08/2014, 80
01/09/2014, 90
01/10/2014, 100
01/11/2014, 110
01/12/2014, 120
];
Final:
Load
*,
If(NumberofDays > 0 and NumberofDays <=90, '3Months') as Bucket
Resident Temp;
Load
*,
If(NumberofDays > 0 and NumberofDays <=180, '6Months') as Bucket
Resident Temp;
Drop Table Temp;
Just re-use Manish's scripts, you can do the calculation on monthly interval.
Temp:
Load
*,
Interval(Month(Today())-Month(Date)) as NumberOfMonths;
Load
Date(Date#(Year,'DD/MM/YYYY')) as Date,
sales
Inline
[
Year, sales
01/01/2014, 10
01/02/2014, 20
01/03/2014, 30
01/04/2014, 40
01/05/2014, 50
01/06/2014, 60
01/07/2014, 70
01/08/2014, 80
01/09/2014, 90
01/10/2014, 100
01/11/2014, 110
01/12/2014, 120
];
Final:
Load
*,
If(NumberOfMonths >=0 and NumberOfMonths <=2, '3Months') as Bucket //3 month bucket include current month
Resident Temp;
Load
*,
If(NumberOfMonths >=0 and NumberOfMonths <=5, '6Months') as Bucket //6 month bucket include current month
Resident Temp;
Drop Table Temp;
Hi,
Another way with the help of date functions we can achieve this
Temp:
Load
Date(Date#(Year,'DD/MM/YYYY')) as Date,
sales
Inline
[
Year, sales
01/01/2014, 10
01/02/2014, 20
01/03/2014, 30
01/04/2014, 40
01/05/2014, 50
01/06/2014, 60
01/07/2014, 70
01/08/2014, 80
01/09/2014, 90
01/10/2014, 100
01/11/2014, 110
01/12/2014, 120
];
Final:
LOAD Distinct *,
if(Date > AddMonths( Today(), -3 ) and Date <= Today(),'3Months') as Bucket
Resident Temp;
LOAD Distinct * ,
if(Date > AddMonths( Today(), -6 ) and Date <= Today(),'6Months') as Bucket
Resident Temp;
DROP Table Temp;
Let me know if any changes required, And you get.
Regards
Anand
Hi,
one solution could be:
tabSales:
LOAD *,
Today()-Date as Numberofdays,
Money(Ceil(Rand()*1000),'$#,##0;($#,##0)') as Sales;
LOAD Date(MakeDate(2010)+Floor(Rand()*(Today()-MakeDate(2010)))) as Date
AutoGenerate 100;
tabIntervals:
LOAD Distinct
Numberofdays,
Dual(3*pow(2,5-IterNo())&' Months',90*pow(2,5-IterNo())) as MonthBucket
Resident tabSales
While 90*pow(2,5-IterNo())>=RangeMax(Numberofdays,90);
hope this helps
regards
Marco
Hi,
you might wish to follow this conversation as well:
How to create the 3 months and 6 months bucket
regards
Marco
Hi All ,
i am using this num(month([Due Date]),'00') as Month_1 for numbers ,
using buckets how to create 3months,6months,9months,1year and 1>year
any suggestions,
thanks