Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have stuck one place please give suggestion any one on this.
My requirement is I have to give selection 3 months bucket and 6 month bucket,
in the edit script I wrote some logic but I am getting 3 months bucket properly but I am not getting 6 month bucket properly.
I need to display the 3 months(90) days data from 0 to 90 days.
I need to display the 6 months (180) days data from 0 to 180 days.
the problem I m getting when I select 6 month it is selecting from 91 to 180(90-180) days but It need to select 0 to 180 days selection.
Please give any suggestion on this.
please find the attachments.
Thanks in advance
Hi,
one solution could be:
tabSales:
LOAD *,
Today()-Date as Numberofdays,
Money(Round(Rand()*1000,0.01)) as Sales;
LOAD Date(MakeDate(2010)+Floor(Rand()*(Today()-MakeDate(2010)))) as Date
AutoGenerate 100;
tabIntervals:
LOAD Distinct
Numberofdays,
Dual('0-'&90*pow(2,5-IterNo())&' days',90*pow(2,5-IterNo())) as DaysBucket
Resident tabSales
While 90*pow(2,5-IterNo())>=RangeMax(Numberofdays,90);
hope this helps
regards
Marco
year:
LOAD
year,
Interval(Today()-num(year),'d') as Numberofdays,
sales
FROM
Monthbucket.xlsx
(ooxml, embedded labels, table is Sheet1);
Load
*,
if(Numberofdays <=90, '3Months') as MonthBucket
Resident year;
Load
*,
if(Numberofdays >0 and Numberofdays <= 180, '6Months') as MonthBucket
Resident year;
DROP Table year;
Hi,
one solution could be:
tabSales:
LOAD *,
Today()-Date as Numberofdays,
Money(Round(Rand()*1000,0.01)) as Sales;
LOAD Date(MakeDate(2010)+Floor(Rand()*(Today()-MakeDate(2010)))) as Date
AutoGenerate 100;
tabIntervals:
LOAD Distinct
Numberofdays,
Dual('0-'&90*pow(2,5-IterNo())&' days',90*pow(2,5-IterNo())) as DaysBucket
Resident tabSales
While 90*pow(2,5-IterNo())>=RangeMax(Numberofdays,90);
hope this helps
regards
Marco
another solution could be:
tabSales:
LOAD *,
Today()-Date as Numberofdays,
Money(Round(Rand()*1000,0.01)) as Sales;
LOAD Date(MakeDate(2013)+Floor(Rand()*(Today()-MakeDate(2013)))) as Date
AutoGenerate 100;
tabIntervals:
LOAD Distinct
Date,
Dual(13-IterNo()&' Months',13-IterNo()) as MonthBucket
Resident tabSales
While AddMonths(Date,13-IterNo())>=Today();
hope this helps
regards
Marco
or
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,
Or another way also with date functions and then create buckets
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.
Ragards
Anand
Thank you very much Marco wedel,
Now it is working fine...
Thank you very much for helping Anand Chouhan,
It is working fine.