Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

sagarh
New Contributor II

Help with Formula

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

1 Solution

Accepted Solutions
jason_michaelid
Honored Contributor II

Re: Help with Formula

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

14 Replies
jason_michaelid
Honored Contributor II

Re: Help with Formula

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

sagarh
New Contributor II

Re: Help with Formula

How do I get my data model?

jason_michaelid
Honored Contributor II

Re: Help with Formula

Ctrl+T - export that to an image.

sagarh
New Contributor II

Re: Help with Formula

export.png

sagarh
New Contributor II

Re: Help with Formula

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
Valued Contributor III

Re: Help with Formula

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_michaelid
Honored Contributor II

Re: Help with Formula

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

sagarh
New Contributor II

Re: Help with Formula

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_michaelid
Honored Contributor II

Re: Help with Formula

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

Community Browser