Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Formula problem

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*

1 Reply
jolivares
Specialist
Specialist

try posting a qvw with sample data to help you.