topic Re: Dealing with 0's / - in QlikView

Dealing with 0's / -

jarrad_murray — Fri, 13 Jul 2018 09:45:53 GMT

Hi All,

I'm hoping someone might be able to help me.

I have a table that is working out a variance between 2 sets of data that have been standardised to a rate per 1,000.

The expression I am using is:

(

sum({<[Main Programme] = {'Neurology'}, [Chapter] = {'Neurological - Conditions'}, POD = {'Elective'}, Year = {'2018-19'}>} [Cost]) //Calculate the current year value for Neurology

/count(distinct {<Year = {'2018-19'}>}Period) //Divide by the current number of months

)

*12 //Times by 12 to get a straight line projection

)

/sum({<Year = {'2018-19'}>}Pops) //Divide this by the current cohort

)*1000 // Times by 1000 to get the RPT

)

-sum({<Year = {'2015-16'}, [Main Programme] = {'Neurology'}, [Chapter] = {'Neurological - Conditions'}, POD = {'Elective'}>}[Best 5]) //Minus the Target vale to find the variance

The problem that I am facing is that I have 9 Dimensions but 3 are blank for the first sum. This is causing the equation to set them as '-' and to be excluded from the final results displayed.

Un-checking the 'Suppress Zero-Values' checkbox causes the table to display every available item regardless of the set analysis of the expression.

Does anyone know how I can get the table to display the 9 values, some as 0 without showing everything (I have 100+ programmes broken down into main programmes and chapters).

Any help you can offer is greatly appreciated.

Re: Dealing with 0's / -

sunny_talwar — Fri, 13 Jul 2018 11:58:06 GMT

Would you be able to share a sample to test it out?

Re: Dealing with 0's / -

jarrad_murray — Fri, 13 Jul 2018 12:05:30 GMT

Hi Sunny,

Please find attached a sample to test out.

Many thanks for your help with this.

Re: Dealing with 0's / -

sunny_talwar — Fri, 13 Jul 2018 12:11:46 GMT

Are you after a view like this?

Re: Dealing with 0's / -

jarrad_murray — Fri, 13 Jul 2018 12:16:34 GMT

Ooh yes, that's it.

Is it the match function that is doing this?

Thank you very much.

Re: Dealing with 0's / -

sunny_talwar — Fri, 13 Jul 2018 12:19:47 GMT

So, I made few changes

1) Used a calculated dimension

=If(Match(Programme, 'Epilepsy', 'Functional Disorders', 'Headache and Migraine', 'Motor Neurone Disease and Spinal Muscular Atrophy', 'Multiple Sclerosis and Inflammatory Disorders',

'Neuromuscular Diseases', 'Parkinsonism and Other Extrapyramidal Disorders', 'Traumatic Brain and Spine Injuries', 'Tumours of the Nervous System'), Programme)

2) Checked 'Suppress When Value is Null' on the dimensions tab

3) Unchecked 'Suppress Zero-Values' from the Presentation tab

Re: Dealing with 0's / -

jarrad_murray — Fri, 13 Jul 2018 12:22:07 GMT

Brilliant,

Thank you so much for your help with this Sunny.