Calculating £'s saved based on start date and no. of months
I've got an excel document we use to track savings.
Each row has a start date (for when the savings start) and the number of months savings will run for. In excel i've got columns for April through to March (the financial year) and excel formulas that show how much of the saving is delivered in that particular month.
e.g.
Total savings is £120k
Start date = 1/6/2014
Duration = 6 months
Hence excel shows.
April - 0
May - 0
June - £20k
July - £20k
Aug - £20k
Sept - £20k
Oct - £20k
Nov - £20k
Dec - 0
Jan - 0
Feb - 0
Mar - 0
The saving may well start at any date in the month, so in the example above if it started 20/6/2014 you'd be wanting to calculate number of days left in the month multiplied by the equivalent daily saving. If this is too difficult i'd force people to default start date to the beginning of the month.
What i need is to upload the saving / start date and duration in months, and QlikView show the apportionment per month. What i've not been able to figure out is how to do this in QV.
You posted this question a few years ago, so I expect you have solved this already. But I have knocked up a sample qvw - see attached.
I hope you don't mind but I used the logo and colours from your corporate website AdviseInc as mentioned in your profile.
I have changed your sample data slightly, by having the real savings ending on 12-Aug-2014 and put in some sample viz's - their sizes may need adjusting as per the screen resolution you standardise on.
