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: 
rcorcoran
Creator
Creator

getting zero target to show in chart

Hi

I have a chart with data showing for areas compared to a target.  this works fine except when the target is 0 then the target line does not show up.

The target is written as an expression as follows:

if(ReferredYYYY=2016, Sum({$<[TargetCode]={"MH39"},ReferredYYYY, TargetYearMonth={">=$(=AddMonths(Max({<[CAMHS121] ={7}>}YearMonth), -12))<=$(=Max({<[CAMHS121] ={7}>}YearMonth))"}>}Target) ,

if(ReferredYYYY=2015, Sum({$<[TargetCode]={"MH-39"},ReferredYYYY, TargetYearMonth={">=$(=AddMonths(Max({<[CAMHS121] ={7}>}YearMonth), -12))<=$(=Max({<[CAMHS121] ={7}>}YearMonth))"}>}Target) ,

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, ''))))

can anyone help as to how I can make the target show if zero.

Thanks

1 Solution

Accepted Solutions
sunny_talwar

May be something like this:

If(

if(ReferredYYYY=2016, Sum({$<[TargetCode]={"MH39"},ReferredYYYY, TargetYearMonth={">=$(=AddMonths(Max({<[CAMHS121] ={7}>}YearMonth), -12))<=$(=Max({<[CAMHS121] ={7}>}YearMonth))"}>}Target) ,

if(ReferredYYYY=2015, Sum({$<[TargetCode]={"MH-39"},ReferredYYYY, TargetYearMonth={">=$(=AddMonths(Max({<[CAMHS121] ={7}>}YearMonth), -12))<=$(=Max({<[CAMHS121] ={7}>}YearMonth))"}>}Target) ,

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, '')))) = 0, 0.0001,

if(ReferredYYYY=2016, Sum({$<[TargetCode]={"MH39"},ReferredYYYY, TargetYearMonth={">=$(=AddMonths(Max({<[CAMHS121] ={7}>}YearMonth), -12))<=$(=Max({<[CAMHS121] ={7}>}YearMonth))"}>}Target) ,

if(ReferredYYYY=2015, Sum({$<[TargetCode]={"MH-39"},ReferredYYYY, TargetYearMonth={">=$(=AddMonths(Max({<[CAMHS121] ={7}>}YearMonth), -12))<=$(=Max({<[CAMHS121] ={7}>}YearMonth))"}>}Target) ,

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, '')))))

View solution in original post

8 Replies
sunny_talwar

May be something like this:

If(

if(ReferredYYYY=2016, Sum({$<[TargetCode]={"MH39"},ReferredYYYY, TargetYearMonth={">=$(=AddMonths(Max({<[CAMHS121] ={7}>}YearMonth), -12))<=$(=Max({<[CAMHS121] ={7}>}YearMonth))"}>}Target) ,

if(ReferredYYYY=2015, Sum({$<[TargetCode]={"MH-39"},ReferredYYYY, TargetYearMonth={">=$(=AddMonths(Max({<[CAMHS121] ={7}>}YearMonth), -12))<=$(=Max({<[CAMHS121] ={7}>}YearMonth))"}>}Target) ,

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, '')))) = 0, 0.0001,

if(ReferredYYYY=2016, Sum({$<[TargetCode]={"MH39"},ReferredYYYY, TargetYearMonth={">=$(=AddMonths(Max({<[CAMHS121] ={7}>}YearMonth), -12))<=$(=Max({<[CAMHS121] ={7}>}YearMonth))"}>}Target) ,

if(ReferredYYYY=2015, Sum({$<[TargetCode]={"MH-39"},ReferredYYYY, TargetYearMonth={">=$(=AddMonths(Max({<[CAMHS121] ={7}>}YearMonth), -12))<=$(=Max({<[CAMHS121] ={7}>}YearMonth))"}>}Target) ,

if(ReferredYYYY=2014, '',

if(ReferredYYYY=2013, '')))))

MayilVahanan

HI

Are you tried like below

Presentation -> uncheck suppress zero values option

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
rcorcoran
Creator
Creator
Author

Thank you Mayil - this worked and also the answer before you - I dont know how to mark both as correct but both worked great for me

rcorcoran
Creator
Creator
Author

Thank you Sunny T once again this works fine

MayilVahanan

Hi

No problem. You got solution know. Cheers

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
sunny_talwar

No problem, but I think you may want to consider using MAYIL‌ way of doing it just because it would save you hassle of using a long formula here

Anonymous
Not applicable

go to properties and go presentation tab ->

In this tab have option supress is zero value enable and click zero on bar

Anonymous
Not applicable

to add the particular column 0.01 than you identify easily