Good day,
I have two tables from where I extract info from - Agreements Table and NL trans Table.
My month and year fields are specified as follow:
DueDate as [Instalment Date],
month(DueDate) as Month,
year(DueDate) as Year,
The Due Date in the data is always on the first data of the month.
NL Trans date have various posting dates.
My income that I want to calculate comes from the Agreements table, and my Cost comes from my NL trans Table.
I used the following formula to calculate my costs: I have a variable = vMaxDate = max([Instalment Date])
sum(if(monthend([Instalment Date])=vMaxDate,if(left(Narrative,16) <> 'FK Mnthly-MAINT-',(ExpenseCode))))
The Maintenance Costs lies in expense code 600 ( I specified in my script that it should only pull data where ExpenseCode = '600')
The NL Trans have various Narratives, I only want it to look at 'FK Mnthly-MAINT- when calculating the costs. But the narrative 'FK Mnthly-MAINT-'has a number afterwards for example 'FK Mnthly-MAINT-13, but I only want it to look at 'FK Mnthly-MAINT-.
But it returns no data, please advice what is wrong with regards to my formula.
Thanks
*R*
