Extract Years and No. of Months of each year from 2 dates

[apthansh]

Here is detailed example that I am trying to achieve.

Data is as below.

ID	Start Date	End Date	Savings
AAA	7/1/2019	1/31/2021	4,000,000

For AAA No. of months b/w start date and end date is 19 Months, out of 19 months 6 Months is in 2019, 12 Months is in 2020, 1 month is in 2021.

I want the savings to be distributed across all these years.so per month savings would be 4,000,000/19 = $ 210,526.
 

 2019 Savings would be 210,526 * 6 = $1,263,157
2020 Savings would be 210,526 *  12 = $2,526,312
2021 Savings would be 210,526 *  1 = $210,526

Bar chart will have 3 years - 2019, 2020, 2021 with the respective savings.Is this doable ?

[Arthur_Fong]

I can get this, but not with savings splitted into years:

[Vegar]

It is possible, with my solution you will need to make adjustment in how you read the data in the script. See script below picture.

SET DateFormat='MM/DD/YYYY';
DATA:
LOAD
	ID,
	[Start Date],
	[End Date],
	Year, 
	Sum(MonthlySavings) as AnnualSavings
group by 
	ID,
	[Start Date],
	[End Date],
	Year,
;
LOAD 
	ID,
	[Start Date],
	[End Date],
	Year(AddMonths([Start Date], IterNo()-1) ) as Year,
	Savings/NoOfMonths as MonthlySavings
While 
	AddMonths([Start Date], IterNo()-1)  <= [End Date];
;
LOAD 
	num(year(AddMonths([End Date],1))*12+month(addmonths([End Date],1)), '000000') - num(year([Start Date])*12 + month([Start Date]),'000000')  as NoOfMonths,
	* 
inline [
ID	Start Date	End Date	Savings
AAA	7/1/2019	1/31/2021	4000000 
BBB	7/1/2018	1/30/2020	1000000
] (delimiter is '	')
;

[Kushal_Chawda]

Another approach

Data:
Load *,
     round(Savings/Num(((year([End Date]) * 12) + month([End Date])) - (((year([Start Date]) * 12) + month([Start Date]))) + 1)) as AnnualSavings,
     Year([Start Date])+IterNo()-1 as Year,
     (year(YearStart([Start Date],IterNo()-1))*12 + if(IterNo()=Year([End Date])- year([Start Date])+1,month([End Date]),month(YearEnd([Start Date]))))-
     (year(YearStart([Start Date],IterNo()-1))*12 + num(if(IterNo()=1,month([Start Date]),month(YearStart([Start Date]))))) +1 as MonthDiff_InYear,
     Num(((year([End Date]) * 12) + month([End Date])) - (((year([Start Date]) * 12) + month([Start Date]))) + 1) as MonthDiff_Overall
While Year([Start Date])+IterNo()-1 <= year([End Date]);
Load * Inline [
ID, Start Date, End Date, Savings
AAA,1/7/2019,31/1/2021,4000000
BBB,1/6/2018,31/1/2020,5000000 ];

Now you can create Bar chart with Dimension Year and below expression

Sum(AnnualSavings*MonthDiff_InYear)

[apthansh]

https://community.qlik.com/t5/New-to-QlikView/Fiscal-Year-expense-calculation/m-p/1691648#M388008

Hi - Any idea how I can achieve this ?

[apthansh]

https://community.qlik.com/t5/New-to-QlikView/Fiscal-Year-expense-calculation/m-p/1691648#M388008

Hi - Any idea how I can achieve that ?Thank you much.