topic Re: getting zero target to show in chart in QlikView

getting zero target to show in chart

rcorcoran — Thu, 28 Jan 2016 10:09:39 GMT

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

Re: getting zero target to show in chart

sunny_talwar — Thu, 28 Jan 2016 10:12:36 GMT

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, '')))))

Re: getting zero target to show in chart

MayilVahanan — Thu, 28 Jan 2016 10:14:32 GMT

Are you tried like below

Presentation -> uncheck suppress zero values option

Re: getting zero target to show in chart

rcorcoran — Thu, 28 Jan 2016 10:18:01 GMT

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

Re: getting zero target to show in chart

rcorcoran — Thu, 28 Jan 2016 10:18:38 GMT

Thank you Sunny T once again this works fine

Re: getting zero target to show in chart

MayilVahanan — Thu, 28 Jan 2016 10:19:27 GMT

No problem. You got solution know. Cheers

Re: getting zero target to show in chart

sunny_talwar — Thu, 28 Jan 2016 10:19:47 GMT

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

Re: getting zero target to show in chart

Anonymous — Thu, 28 Jan 2016 10:20:20 GMT

go to properties and go presentation tab ->

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

Re: getting zero target to show in chart

Anonymous — Thu, 28 Jan 2016 10:25:41 GMT

to add the particular column 0.01 than you identify easily