Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I'm fadcing issue with number of days calculation, can you pelase have a look below scenario..
Example: I have start date and end date, here have to calculate Current Year Number of days and Previous year number of days..
Category | SatrtDate | EndDate | Period |
A | 1/1/2018 | 5/22/2019 | Jan-2018 - May-2019 |
B | 8/22/2019 | 12/31/2020 | Aug-2019 - Dec-2020 |
Example 1 : Period =Jan-2018 - May-2019
StartDate = 1/1/2018 and EndDate = 5/22/2019
Current : NUll (we don't have current year)
Previuos Year = 5/22/2019 (May 22nd 2019 - so we have to calculate Year start from May 22 = 141 days )
Example 2: Period = Aug-2019 - Dec-2020
Start Date = 8/22/2019 and EndDate = 12/31/2020
Current Year = 12/31/2020 (Dec 31st 2020 - thus we have to calculate Year Start from 12th Dec = 365 days)
Previous Year = 8/22/2019 (Aug 22nd2019 - so we have to calculate Aug22nd to Dec31 2019 = 131 days (8-22-2019 - 12-31-2019 =131 days))
OutPut:
Category | SatrtDate | EndDate | Period | CurrentYearNoofDays | PreviuosYearNoofDays |
A | 1/1/2018 | 5/22/2019 | Jan-2018 - May-2019 | 141 | |
B | 8/22/2019 | 12/31/2020 | Aug-2019 - Dec-2020 | 365 | 131 |
Thanks in Advance!
attached a version
you can change in the variables to adjust the results
let CurrentYear=Date#(YearStart(today()),'MM/DD/YYYY');
let PreviousYear=Date#(YearStart(Makedate(Year(today())-1,1,1)),'MM/DD/YYYY');
let CurrentYear0=Date#(YearEnd(today()),'MM/DD/YYYY');
let PreviousYear0=Date#(YearEnd(Makedate(Year(today())-1,1,1)),'MM/DD/YYYY');
Data:
load *,
if(Year('$(CurrentYear)')=Year(SatrtDate),SatrtDate-'$(CurrentYear)',if(Year('$(CurrentYear)')=Year(EndDate),EndDate-'$(CurrentYear)')) as CurrentYearNoofDays,
if(Year('$(PreviousYear0)')=Year(SatrtDate),'$(PreviousYear0)'-SatrtDate,if(Year('$(PreviousYear0)')=Year(EndDate),EndDate-'$(PreviousYear)')) as PreviuosYearNoofDays
;
load Category, Date#(SatrtDate,'MM/DD/YYYY') as SatrtDate, Date#(EndDate,'MM/DD/YYYY') as EndDate, Period inline [
Category, SatrtDate, EndDate, Period
A, 1/1/2018, 5/22/2019, Jan-2018 - May-2019
B, 8/22/2019, 12/31/2020, Aug-2019 - Dec-2020
];
can you correct me ?
Here is what I'm getting.
why once you compare with the end of year and once with start of year
Exactly what I need,
as per my scenario need that caluclation.
Current Year need to be caluclated YearStart and previous year as on date to end of year.
attached a version
you can change in the variables to adjust the results
let CurrentYear=Date#(YearStart(today()),'MM/DD/YYYY');
let PreviousYear=Date#(YearStart(Makedate(Year(today())-1,1,1)),'MM/DD/YYYY');
let CurrentYear0=Date#(YearEnd(today()),'MM/DD/YYYY');
let PreviousYear0=Date#(YearEnd(Makedate(Year(today())-1,1,1)),'MM/DD/YYYY');
Data:
load *,
if(Year('$(CurrentYear)')=Year(SatrtDate),SatrtDate-'$(CurrentYear)',if(Year('$(CurrentYear)')=Year(EndDate),EndDate-'$(CurrentYear)')) as CurrentYearNoofDays,
if(Year('$(PreviousYear0)')=Year(SatrtDate),'$(PreviousYear0)'-SatrtDate,if(Year('$(PreviousYear0)')=Year(EndDate),EndDate-'$(PreviousYear)')) as PreviuosYearNoofDays
;
load Category, Date#(SatrtDate,'MM/DD/YYYY') as SatrtDate, Date#(EndDate,'MM/DD/YYYY') as EndDate, Period inline [
Category, SatrtDate, EndDate, Period
A, 1/1/2018, 5/22/2019, Jan-2018 - May-2019
B, 8/22/2019, 12/31/2020, Aug-2019 - Dec-2020
];