Help with Formula

[Anonymous]

Hi

I have the following formula:-

sum({$<FiscalYear={2012}>}if (linetype='Invoiced',(val),0))

this sets the year to being 2012 and gives me the sum of all lines invoiced...this is returning a different number to the following formula

(if (linetype='Invoiced',(val),0))

where I have selected the year 2012 from a list box...Any ideas why this should be happening?

Thanks for any help!!

[jvitantonio]

Ok, in that case you don't need to use the function SUM() but the function  ONLY(). Try this:

only({<FiscalYear={2012},linetype={'Invoiced'}>} val)

[Anonymous]

Hi This works but where I have a straight table it returns no values at all yet the formula with SUM does return a value (albeit it is the wrong amount)

[Jason_Michaelides]

I would advise you fix your data model.  If a record is doubling up and it shouldn't then there is a problem - don't cover it up by using a different expression.

[jvitantonio]

Sorry, you are not making sense. What value do you want to see? If you want to see 26600 then use Sum, otherwise use Only. As Jason say you model is inconsistent. You should have duplicate rowswith exact same information. This show be aggregated.

[Anonymous]

Apologies!! I have made the mistake and as you say Jason I had incorrect data - I should have looked at it more thoroughly than just assume I had it right!

So once again thanks for the Help - I now also know about the ONLY function - thanks Qlikuser14!

Best Regards

Sagar