Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

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

Here is detailed example that I am trying to achieve.

Data is as below.

IDStart DateEnd DateSavings
AAA7/1/20191/31/20214,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 ?

5 Replies
Arthur_Fong
Partner
Partner

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

MC.PNG

 

Vegar
Partner
Partner

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.

image.png

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 '	')
;

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Kush
MVP
MVP

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
Creator
Creator

apthansh
Creator
Creator