Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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, '')))))
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, '')))))
HI
Are you tried like below
Presentation -> uncheck suppress zero values option
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
Thank you Sunny T once again this works fine
Hi
No problem. You got solution know. Cheers
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
go to properties and go presentation tab ->
In this tab have option supress is zero value enable and click zero on bar
to add the particular column 0.01 than you identify easily