Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
richnorris
Creator II
Creator II

Hide 0 Values in cumulative line chart

I have a line chart that shows current year vs previous year growth. I want to display all months, but for the current year obviously we don't have data for all months. I want to accumulate to show the growth, but I don't want a line for the current year if there is no data, I want it to stop.

If I don't accumulate, its quite easy, because you can just do an

'if(sum(CurrentYearData) > 0, sum(CurrentYearData), '')'.

This is what I want to see. However, I want to accumulate. This causes a problem, because the value ISN'T 0, its the accumulation of all months so far.

I was thinking it might be possible to do something along the lines of

'If(Sum(CurrentYearData) = previousValue(sum(CurrentYearData)), '', sum(CurrentYearData))'

but I don't know how I would get 'previousValue()'. Note that the dimension is dynamic based on a variable, so it has to actually be previousValue() rather than a way of getting the previous month, as it could be weeks or days instead.

Anyone have any good ideas? Thanks in advance!

1 Solution

Accepted Solutions
richnorris
Creator II
Creator II
Author

Hi,

Just in case anyone else has this problem, the solution is to change the 'Background Color' of the line you want to hide, and then do

if(*EXPRESSION-OF-LINE* = 0, ARGB(0,0,0,0))

So it will still actually calculate it, but it makes it transparent where the value is 0.

View solution in original post

4 Replies
makkemik
Partner - Contributor III
Partner - Contributor III

Create a simple inner table (2 col.) table first like;

1, Jan

2,Feb,

3,Mar

....

Then join it with your main fact table then use this table field (Jan...)  in your chart also dont dorget to clear suppress zero value check box in your line chart.

It shoul help you

Regards

Murat

sudeepkm
Specialist III
Specialist III

maybe you can show months when it is previous year (like jan2013, feb2013 so on) and just show year on your chart when it is the current year. (for jan2014, feb 2014 and so on just show 2014)

richnorris
Creator II
Creator II
Author

I show both years on the same chart, the idea is that you can compare the trend of this year vs last year on the same chart, while still being able to see the full year for last year. Showing only one year at a time wont really help. If you mean to say, for current year show a total for the year, that wont really be ideal because then you will be massively misrepresenting the data, it would show full year for Jan, then accumulate and show twice the full year for Feb. If you turn off the accumulation, then you would just get a straight line, since the value (full year total) would be the same for every month, which isn't very useful for doing trend analysis.

I can't just create some table with values for months, because, as I said, the dimension is actually dynamic. It wont always show month. When it shows days, weeks, quarters, etc, this wont work, unless I create a seperate table for each possible value. That's not especially elegant, there has to be a better way to just not show a line for a certain condition?

Thank you both for your suggestions though!

richnorris
Creator II
Creator II
Author

Hi,

Just in case anyone else has this problem, the solution is to change the 'Background Color' of the line you want to hide, and then do

if(*EXPRESSION-OF-LINE* = 0, ARGB(0,0,0,0))

So it will still actually calculate it, but it makes it transparent where the value is 0.