topic Re: Chart SUM Expression Help in QlikView

Chart SUM Expression Help

rebelfox — Mon, 21 Sep 2015 07:58:52 GMT

I have a bar chart on an inherited QlikView report with the following expression:-

Sum ({$<[F_Year]={">2006"}>}[Invoice_Net] )

Not sure I understand the syntax. SUM is clearly a total.

Invoice_Net is the value to total but the literal ">2006" is a bit odd.

Is this an if Condition stating that is F_Year = ">2006" then Sum Invoice_Net?

The chart is showing 'No data to display' regardless of what selections I make so maybe this is the cause.

Re: Chart SUM Expression Help

settu_periasamy — Mon, 21 Sep 2015 08:05:36 GMT

Expression seems to be fine. may be try with single quote..or check the F_Year (case sensitive)

Sum({$<[F_Year]={'>2006'}>}[Invoice_Net])

Re: Chart SUM Expression Help

sasiparupudi1 — Mon, 21 Sep 2015 08:06:30 GMT

Yes the expression is telling qv to sum the invoce_Net values for all the F_Year values that are greater than 2006.

do you have F_Year values as 2005, 2006,2007 etc? if yes then the expression should give your results..

Re: Chart SUM Expression Help

rebelfox — Mon, 21 Sep 2015 08:17:09 GMT

So SUM does accept a condition as the first parameter?

I have dragged a List Box on to the sheet showing F_Year and it shows possible values as

'2007-2008'

'2008-2009'

'2009-2010'

'2011-2012'

'2014-2016'

But no selection of these values show anything on the charts.

I removed the {$<[F_Year]={'>2006'}>} part and I get values in the chart so it seems to be somehow the source of the problem. I also tried changing the " around the >2006 to ' but this had no impact.

Re: Chart SUM Expression Help

settu_periasamy — Mon, 21 Sep 2015 08:24:16 GMT

Hi,

it should work if your year field have the single value like

2006

2007

2008

...

2016

do you have any other year field related with F_Year, if yes, you can put that..

Re: Chart SUM Expression Help

IAMDV — Mon, 21 Sep 2015 08:28:40 GMT

Roy - Your comprehension is correct. Have you tried using 1 instead of $:

Sum ({1<[F_Year]={">2006"}>} [Invoice_Net] )

Just to be clear:

This will SUM all the Invoice_Net rows where F_Year is greater than 2006.

Hope this helps!

Cheers,

www.QlikShare.com

Re: Chart SUM Expression Help

rebelfox — Mon, 21 Sep 2015 08:30:45 GMT

What would the '1' do?

Re: Chart SUM Expression Help

sasiparupudi1 — Mon, 21 Sep 2015 08:33:22 GMT

Inorder for your expression to work, you should have a dimension that has values like 2006,2007...2015

if you want your expression to work on the F_Year,

try

Sum ({$<[F_Year]={">='2007-2008'"}>}[Invoice_Net] )

hth

Sasi

Re: Chart SUM Expression Help

simenkg — Mon, 21 Sep 2015 08:41:06 GMT

Just change it to Sum([Invoice_Net]) and use a list box to select which F_Years to show.

If you want to see all F_Years regardless of selection in the field, then change it to Sum({$<F_Year]=>} [Invoice_Net])

If you only want to show the recent years, then you are probably better off filtering these values in the load script as this will increase the overall performance of the model.

Re: Chart SUM Expression Help

rebelfox — Mon, 21 Sep 2015 09:05:32 GMT

Thanks all.

I found a copy of the report from 6 months ago.

It also had the same problem.

I think this report was written in haste and not tested.

The user has only just started to use the three sheets with problems.

I even found one sheet comparing to another literal that was spelt incorrectly so I am pretty confident it's just a case of remove the '>=2006' condition.