Yearly Totals from an Expense Schedule

[Anonymous]

Hi Qlikview Experts,

I worked on a script that intends to total costs by year from a table of cost schedules. The script is modified from something I got here in the community. It appears to be working well most of the time. On those occasions that it is miscalculating, I cannot seem to figure out the reason. Hope someone can advise.

In the attached example, if working properly these are the expected outputs for year 2016:

File ID	Expected Sum for 2016	Comments
ID-123	99,600	This is working correctly
ID-999	86,400	This is working correctly
ID-000	1,868,400	This is calculating incorrectly. On the chart it is double this number.

Thanks for the help. Attached are the relevant files.

[swuehl]

I think the JOIN incorrectly duplicated some facts. Would need to take another look how this can be improved.

Are these numbers correct?

[Anonymous]

Hi swuehl, we seem to be very close now. It resolved the miscalculation on year 2016 for ID-000 and also resolved other things I didn't realize earlier.

The only thing that is amiss is year 2020 for ID-123. It seems to be caused by the fact that there are two schedules that occur on that year. One "payment" schedule ends 7/31/2020 and another picks up from 8/1/2020. The total for 2020 for ID-123 should be 105600. On the chart it shows as 213600.

[swuehl]

Maybe have a look at

Creating Reference Dates for Intervals

and try to break down your yearly payments to your monthly reference dates (something what you are currently doing in the chart expression, using the if() statement.

[Anonymous]

Thanks for sharing. The link points to a different approach. Trying my luck with it and fitting it to my script but having difficulties. I think the existing one is pretty close though I am similarly not getting any success resolving the issue on year 2020.

[Anonymous]

Anyone have any suggestions or can provide assistance? Thanks!

[swuehl]

Please check this sample, it's something along the lines Henric described in the post 'creating reference dates for intervals'. As a further improvement, the costs could be distributed to the single months, so we can get rid of the if() statement checking payment frequency in the expression.

[Anonymous]

Still playing around with it, but I must say looking great so far!

When you mean getting rid of the if() statement on the expression, I suppose it would be by leveraging the MonthMultiplicator you added on the load script. Can you expound on this further? Do I remove the if() statement from the expression and transfer the if() statement on the load script to define the respective values of MonthMultiplicator for each frequency i.e. 1 for Monthly, 1/3 for Quarterly, 1/12 for Annually?

[swuehl · Accepted solution]

Bringing the cost values to the link table.

See attached. The one-time payments are currently not considered, so this is just a demo of what I am talking about.