Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
I can get this, but not with savings splitted into years:
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 ' ')
;
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)
https://community.qlik.com/t5/New-to-QlikView/Fiscal-Year-expense-calculation/m-p/1691648#M388008
Hi - Any idea how I can achieve this ?
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.