Supress zeros when values are Null or not available in line chart

[kishorj1982]

Hi

I have a LINE chart with 2 expressions and 1 dim.

1 Expression = Last Year values while 2nd expression contains this year values. Problem is, suppost for this year I am in the week 8, I will have data till 7th week but for Last year I have data for all the weeks. Now, I dont want to show the week 8,9,10 values in the line chart for this year as "Zero". Qlikview assumes zeros for null values I guess.

Also, I dont want to supress Zero in the presentation tab as it create confusion in line chart when we have real Zero Values in any of the week.

Please have a look in the attached and suggest some solution.

Thanks

[sunny_talwar]

May be use this expression for TY:

If(Len(Trim(TY)) > 0, Sum(TY))

[kishorj1982]

Thanks Sunny. Real life scenario for me was not that straight forward. I had Set analysis in the expression and when I was applying If condition on it, it was not giving me correct results. Basically Set analysis was not giving correct results after applying If condition. I have put the Same set analysis in the If statement as well and it worked finally.

[ToniKautto · Accepted solution]

In your example there is no data for weeks 8 and forward for the current year. If you aggregate the sum of this period you mathematically must get a zero result.

In the simplest example you can see this by aggregating =Sum(Null()), which will return 0.

By applying an IF statement and only aggregating the expression for the desired range you can get the null values.

=If(Week<8, Sum(TY))

Alternatively you can have a variable that aggregates the current week so that the range is always ads expected.

=If(Week<$(#vCurrentWeekNo), Sum(TY))

Just to clarify, I do not think you see any product problem in theses scenarios. You are getting the logically correct results in these scenarios, however they do not match you expectations.

If you are happy with your question being answered  and the issue has been resolved in your application, please mark this thread as answered to indicate it requires no further attention from Qlik Community members.