topic Re: Help with Formula in QlikView

Help with Formula

Anonymous — Wed, 05 Sep 2012 09:14:39 GMT

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

Re: Help with Formula

Jason_Michaelides — Wed, 05 Sep 2012 09:26:41 GMT

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

Re: Help with Formula

Anonymous — Wed, 05 Sep 2012 09:32:36 GMT

How do I get my data model?

Re: Help with Formula

Jason_Michaelides — Wed, 05 Sep 2012 09:33:30 GMT

Ctrl+T - export that to an image.

Re: Help with Formula

Anonymous — Wed, 05 Sep 2012 09:39:27 GMT

Re: Help with Formula

Anonymous — Wed, 05 Sep 2012 09:46:45 GMT

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

Re: Help with Formula

jvitantonio — Wed, 05 Sep 2012 09:53:56 GMT

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.

Re: Help with Formula

Jason_Michaelides — Wed, 05 Sep 2012 09:54:44 GMT

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

Re: Help with Formula

Anonymous — Wed, 05 Sep 2012 11:14:01 GMT

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.

Re: Help with Formula

Jason_Michaelides — Wed, 05 Sep 2012 11:19:44 GMT

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

Re: Help with Formula

jvitantonio — Wed, 05 Sep 2012 12:09:42 GMT

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)

Re: Help with Formula

Anonymous — Wed, 05 Sep 2012 12:45:38 GMT

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)

Re: Help with Formula

Jason_Michaelides — Wed, 05 Sep 2012 12:47:17 GMT

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.

Re: Help with Formula

jvitantonio — Wed, 05 Sep 2012 12:52:48 GMT

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.

Re: Help with Formula

Anonymous — Wed, 05 Sep 2012 13:46:37 GMT

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