Date range

[upaliwije]

I have some sales data stored in a table in date order

Date                Sales

01-01-2017     5000

02-01-2017    10000

......

31-08-2017    12000

I want to extract data for the following Periods

1.   1st to 14th of the month  Eg 01-01-17 to 14-01-17

2.   15th to 31st  of the month Eg: 15-01-17 to 31-01-17

3.    15th to 14th (two months) Eg : 15-01-17 to 14-02-17

How can I do this in script or master calendar pls help

[Anil_Babu_Samineni]

Try some thing like below?

Fact:

Load Date, Sales from Fact;

Concatenate (Fact)

Load If(Date >= '01-01-17' and <='14-01-17', Date) as Date,

          Sales,

          'Period 1' as Flag

Resident Fact;

Concatenate (Fact)

Load If(Date >= '15-01-17' and <='31-01-17', Date) as Date,

          Sales,

          'Period 2' as Flag

Resident Fact;

Concatenate (Fact)

Load If(Date >= '15-01-17' and <='14-02-17', Date) as Date,

          Sales,

          'Period 3' as Flag

Resident Fact;

[upaliwije]

Hi Anil,

I can do what you suggest for small date range .But In my data table I have millions of records ranging 1 -15 years. So it is practically difficult to do it.Is there a way to do it through master calendar ?

[Anil_Babu_Samineni]

Even, If you need master calendar off course, Bucket needed separately. And i believe you don't require master calendar over here. Can you provide more information related the same..

[upaliwije]

Anil

I have attached my sample data .I want Period Brackets as shown above to be against RISK_DATE. Kindly help me with best possible solution

[Kushal_Chawda]

Your buckets are fixed? I mean, 1 to 15, 15 to 31,  15 to 14 for two months?

[upaliwije]

Yes

[Kushal_Chawda]

Is it ok if the same can be performed on front end, instead creating Flag in script?

[upaliwije]

Better If could be done as script level, Anyway pls make available your suggested method

[Anonymous]

hi I have Used this Logic with Demo of Master calender and its working fine Load If(Date >= '01/01/2017' and Date <='14/01/2017', Date) as Date,
'Period 1' as Flag

Resident MasterCalendar;

Load If(Date >= '15/01/2017' and Date<='31/01/2017', Date) as Date,
'Period 2' as Flag

Resident MasterCalendar;

Load If(Date >= '15/01/2017' and Date <='14/02/2017', Date) as Date,
'Period 3' as Flag

Resident MasterCalendar;