topic Re: Formula error in QlikView

Formula error

Tue, 20 Aug 2013 13:58:12 GMT

Good day,

I need help.

I want to calculate our Rental Contracts Maintenance Costs.

The costs Lies in out General Ledger. In this table we have a field name: Narrative.

In this Narrative I want the sum of my formula to exclude any entries with FK Mnthly-MAINT-23.

[The 23 at the end indicate how many months this rental has been running].

Thus, it should sum all the enties values, except the values with a Narrative FK Mnthly-MAINT...

I am using the following formula:

sum(if(left(Narrative,16)<>'FK Mnthly-MAINT-',Value)) , but it looks like it is still pulling through the values with this Narrative.

Please advice if my formula is incorrect, and what the correct manner will be?

Thank you

*R*

Tue, 20 Aug 2013 14:02:45 GMT

The Narrative field must be aggregated somehow, like all the fields in expressions in qlikview. You can do it with the Only function:

sum(if(left(Trim(Only(Narrative)),16)<>'FK Mnthly-MAINT-',Value))

Anyway, It would be much better (for performance) to create a new field in the script to check what you want.

Tue, 20 Aug 2013 14:16:57 GMT

Hi there,

If I try that formula, it gives me zero value.

But in my Edit Expression Window it shows that there is an error in Expression:

Tue, 20 Aug 2013 14:25:05 GMT

Sorry Rentia, I didn't realize that you can't trim after the only function, it would be better to try it this way:

sum(if(left(Trim(Narrative),16)<>'FK Mnthly-MAINT-',Value))