Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Team - I have the below data table as an Ex.
ID | Exp | Start Date | End Date |
1 | 10,000 | 3/31/2020 | 3/31/2023 |
Here - I have to pro-rate and divide the expense based on the start date and end date. I have do distribute expense based on expense and no of months in each year.Here we have 10,000 exp and in 2020 we have 9 months and from 2021 - 2022 12 months in each year and in 2023 we have 3 months...so amount would distribute as below.
ID | Exp | Start Date | End Date | 2020 | 2021 | 2022 | 2023 |
1 | 10,000 | 3/1/2020 | 3/31/2026 | 2493 | 3324 | 3324 | 831 |
all these years will total upto 10,000...is this doable ?
Thank you much.
I have to create something similar and have been avoiding it, seeing your question made me tackle it for the both of us:
[RawData]:
LOAD * INLINE
[
ID;Exp;Start Date;End Date
1;10000;3/1/2020;3/31/2026
2;15000;5/1/2021;5/31/2028
](delimiter is ';');
left join (RawData)
LOAD
ID,
Interval([End Date]-[Start Date],'d')+1 as TotalDays,
(Exp)/(Interval([End Date]-[Start Date],'d')+1) as Exp_PerDay
Resident RawData;
MinMaxYear:
LOAD
year(min(date([Start Date]))) as MinYear,
year(min(date([End Date]))) as MaxYear
Resident [RawData];
for year=peek('MinYear',0,'MinMaxYear') to peek('MaxYear',0,'MinMaxYear')
left join (RawData)
LOAD
ID,
if(year([Start Date])>$(year),0,
if(year([Start Date])<$(year) and year([End Date])>$(year),(interval(MakeDate($(year),12,31)-MakeDate($(year),1,1),'d')+1)*Exp_PerDay,
if(year([Start Date])=$(year) and year([End Date])>$(year),(interval(MakeDate($(year),12,31)-[Start Date],'d')+1)*Exp_PerDay,
if(year([Start Date])<$(year) and year([End Date])=$(year),(interval([End Date]-MakeDate($(year),1,1),'d')+1)*Exp_PerDay
))))as $(year)
Resident RawData;
next
I have to create something similar and have been avoiding it, seeing your question made me tackle it for the both of us:
[RawData]:
LOAD * INLINE
[
ID;Exp;Start Date;End Date
1;10000;3/1/2020;3/31/2026
2;15000;5/1/2021;5/31/2028
](delimiter is ';');
left join (RawData)
LOAD
ID,
Interval([End Date]-[Start Date],'d')+1 as TotalDays,
(Exp)/(Interval([End Date]-[Start Date],'d')+1) as Exp_PerDay
Resident RawData;
MinMaxYear:
LOAD
year(min(date([Start Date]))) as MinYear,
year(min(date([End Date]))) as MaxYear
Resident [RawData];
for year=peek('MinYear',0,'MinMaxYear') to peek('MaxYear',0,'MinMaxYear')
left join (RawData)
LOAD
ID,
if(year([Start Date])>$(year),0,
if(year([Start Date])<$(year) and year([End Date])>$(year),(interval(MakeDate($(year),12,31)-MakeDate($(year),1,1),'d')+1)*Exp_PerDay,
if(year([Start Date])=$(year) and year([End Date])>$(year),(interval(MakeDate($(year),12,31)-[Start Date],'d')+1)*Exp_PerDay,
if(year([Start Date])<$(year) and year([End Date])=$(year),(interval([End Date]-MakeDate($(year),1,1),'d')+1)*Exp_PerDay
))))as $(year)
Resident RawData;
next
This is awesome...is there a way to group all years and add Year as a filter or dimension ?
Using the above Script will create a Dimension for each year from your Min Start Year to your Max End Year.
You could then crosstable this into a new table.... which may be the best for Data Analysis
so you would have a new table with the EXP by YEAR:
ID YEAR EXP
1 2020 2493
1 2021 3324
1 2022 3324
1 2023 831