getting zero target to show in chart

[rcorcoran]

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

[sunny_talwar · Accepted solution]

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

[rcorcoran]

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]

Thank you Sunny T once again this works fine

[MayilVahanan]

Hi

No problem. You got solution know. Cheers

[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]

go to properties and go presentation tab ->

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

[Anonymous]

to add the particular column 0.01 than you identify easily