Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

markramiro
Contributor II

Yearly Totals from an Expense Schedule

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 IDExpected Sum for 2016Comments
ID-12399,600This is working correctly
ID-99986,400This is working correctly
ID-0001,868,400This is calculating incorrectly. On the chart it is double this number.

Thanks for the help. Attached are the relevant files.

1 Solution

Accepted Solutions
MVP
MVP

Re: Yearly Totals from an Expense Schedule

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.

View solution in original post

8 Replies
MVP
MVP

Re: Yearly Totals from an Expense Schedule

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

Are these numbers correct?

markramiro
Contributor II

Re: Yearly Totals from an Expense Schedule

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.

MVP
MVP

Re: Yearly Totals from an Expense Schedule

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.

markramiro
Contributor II

Re: Yearly Totals from an Expense Schedule

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.

markramiro
Contributor II

Re: Yearly Totals from an Expense Schedule

Anyone have any suggestions or can provide assistance? Thanks!

MVP
MVP

Re: Yearly Totals from an Expense Schedule

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.

markramiro
Contributor II

Re: Yearly Totals from an Expense Schedule

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?

MVP
MVP

Re: Yearly Totals from an Expense Schedule

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.

View solution in original post