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: 
mattdt1811
Contributor III
Contributor III

Forcing 0 Values into Line Chart

Hi All,

I am struggling to get a line chart of mine to show zero values.

I have tried un-ticking Suppress When Value is Null & Supress Zero Values but it still does not work.

I have a master calendar to generate missing dates in my script and need to insert these values with a 0.

The expression in the chart I have tried to do this with is below but this does not work;

=if ( count({$<FreightPaid={'Paid in UK'},INSURED={'INSURED'}>}JOBCOUNTER) > 0 , (Count({$<FreightPaid={'Paid in UK'},INSURED={'INSURED'}>}JOBCOUNTER)), 0 ) 

I have also been advised I can add one dummy zero record into the fact table for each month by using a calendar and concat it back to the original table but I do not know how to do this.

I have attached an example QVW.

For reference on selection of 2017 there was 1 job in Sep - I want the line chart to show 0 value for all the remaining months on one continuous line to get a better idea of the trend.

Thanks in advance.

1 Solution

Accepted Solutions
trdandamudi
Master II
Master II

Is this what you want ?

ZeroLinegraph.jpg

View solution in original post

4 Replies
trdandamudi
Master II
Master II

Is this what you want ?

ZeroLinegraph.jpg

mattdt1811
Contributor III
Contributor III
Author

Hi Thirumala,

Yes this does work a lot better thanks. However it is showing as below when I insert this into my master QVW – I think this is where the User ID has raised no jobs for the month.

qlikchart.jpg

I have also been advised this can be solved by using the below script beneath the Temp Calendar Script;

Concatenate(TEMP1)
load 'Paid in UK' as FreightPaid,
'Insured'
as INSURED,
TempDate as [CREATION DATE],
'BALDWIN@M' 
as [USER CREATED BY],
1
as Dummy,
0
as JOBCOUNTER
Resident TempCalendar;

Drop Table TempCalendar;  

And then using:

=sum({$<FreightPaid={'Paid in UK'},INSURED={'INSURED'}>}JOBCOUNTER)

As the expression in the chart.

This does work for my own user ID however I will need to get this to work for all User ID’s, Customers, Locations etc.

Do you know how I would do this?

Many Thanks.

trdandamudi
Master II
Master II

Not 100% sure but can you give a try with the below expression:

=sum({$<FreightPaid={'Paid in UK'},INSURED={'INSURED'}>}JOBCOUNTER) * AVG(1)

mattdt1811
Contributor III
Contributor III
Author

Unfortunately this generates the same result, however it is still a lot better than what I was using previously.

Thanks for your help.