Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Until how may conditions you need to develop? Currently, It showing 3
Hi Upali,
See Attachment.
I changed Section 'Calendar' of Script.
Remove Replace from RISKCALENDAR Table in Script.
Regards,
Antonio
only 3
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;
It seems & is missing
,'15-'&MAX(RISK_DATE)&'-'&Month(RISK_DATE))) as Period,
Thanks Antonio
Then I get below error, I can't figure out how to correct it. pls help
You can't use Max(RISK_DATE) because You'd need Group By
Thanks
If you pls can write me script for it
Day(MonthEnd(RISK_DATE)) instead of Max(RISK_DATE)