Chart SUM Expression Help

[rebelfox]

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.

[settu_periasamy]

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

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

[sasiparupudi1]

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

[rebelfox]

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.

[settu_periasamy]

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

[IAMDV]

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,

DV

www.QlikShare.com

[rebelfox]

What would the '1' do?

[sasiparupudi1]

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

[simenkg · Accepted solution]

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.

[rebelfox]

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.