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]

Until how may conditions you need to develop? Currently, It showing 3

[antoniotiman · Accepted solution]

Hi Upali,

See Attachment.

I changed Section 'Calendar' of Script.

Remove Replace from RISKCALENDAR Table in Script.

Regards,

Antonio

[upaliwije]

only 3

[upaliwije]

Your Solution seems alright and I have modified your script to be more user friendly as follows. The underlined part I can not write properly. Pls check and Advise. I ma selecting max(Risk_rate) because in certain months you do get 31st day Eg Feb

LOAD

    Day(RISK_DATE) AS RISK_DAY,

    Date(RISK_DATE) AS RISK_DATE,

    Week(RISK_DATE) AS RISK_WEEK,

    Year(RISK_DATE) AS RISK_YEAR,

    Month(RISK_DATE) As RISK_MONTH,

    Month(RISK_DATE) &'-'& YEAR(RISK_DATE) As RISK_Month_Year,

    Month(RISK_DATE) &'-'& week(RISK_DATE) As RISK_Month_Week,

    If(Day(RISK_DATE) >= 1 and Day(RISK_DATE) <= 14,'1-14 '&Month(RISK_DATE),

If(Day(RISK_DATE) >= 15 and Day(RISK_DATE) <= 31,'15-'&MAX(RISK_DATE)''&Month(RISK_DATE))) as Period,

If(Day(RISK_DATE) >= 15 and Day(RISK_DATE) <= 31,'15 '&Month(RISK_DATE)&'-14 '&Month(AddMonths(RISK_DATE,1)),

'15 '&Month(AddMonths(RISK_DATE,-1))&'-14 '&Month(RISK_DATE))as PERIOD1

    ;

  Load Date(TRN_MinDate + IterNo() -1 ) AS RISK_DATE While (TRN_MinDate + IterNo() - 1) <= Num(TRN_MaxDate);

Load

    Min(RISK_DATE) AS TRN_MinDate,

    Max(RISK_DATE) AS TRN_MaxDate  

RESIDENT growth;

[antoniotiman]

It seems & is missing

,'15-'&MAX(RISK_DATE)&'-'&Month(RISK_DATE))) as Period,

[upaliwije]

Thanks Antonio

Then I get below error, I can't figure out how to correct it. pls help

[antoniotiman]

You can't use Max(RISK_DATE) because You'd need Group By

[upaliwije]

Thanks

If you pls can write me script for it

[antoniotiman]

Day(MonthEnd(RISK_DATE))  instead of Max(RISK_DATE)