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!!

[Jason_Michaelides]

Can you post your data model?

And as an aside, a better expression would be:

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

Hope this helps,

Jason

[Anonymous]

How do I get my data model?

[Jason_Michaelides]

Ctrl+T - export that to an image.

[Anonymous]

[Anonymous]

Please see attached report.

I have reduced the data and removed all other charts etc so it is reduced in size but does show the issue quite clearly....

[jvitantonio]

Ok, the thing is you have 2 lines in your fact table with value 13300, so the correct value is 26600. But in your table this 2 lines are shown as 1 and that's why your IF statement is showing only 1 value = 13300.

[Jason_Michaelides]

You're missing the Sum() around your test expression.  There are two lines with 13300.

[Anonymous]

Hi

Thanks for the help - I appreciate it!

The problem is that the line should only read 13300 yet it is doubling it up to 26600 it is not doing the same for the other two lines...

I have the physical invoice in my hand and we invoiced that line correctly at 13300 yet Qlikview with this formula is showing double...I cannot understand this.

[Jason_Michaelides · Accepted solution]

I suspect it is to do with the left join you are using to join stock to sales orders in your script.  If you have 2 stock lines that link to this sales order then the rows will double up.  Check that out....