Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
rebelfox
Creator
Creator

Chart SUM Expression Help

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.

1 Solution

Accepted Solutions
simenkg
Specialist
Specialist

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.

View solution in original post

9 Replies
settu_periasamy
Master III
Master III

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
Master III
Master III

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
Creator
Creator
Author

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
Master III
Master III

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
Luminary Alumni
Luminary Alumni

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
Creator
Creator
Author


What would the '1' do?

sasiparupudi1
Master III
Master III

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

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
Creator
Creator
Author

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.