Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jarrad_murray
Contributor III
Contributor III

Dealing with 0's / -

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.

Pic.png

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.

1 Solution

Accepted Solutions
sunny_talwar

Are you after a view like this?

Capture.PNG

View solution in original post

6 Replies
sunny_talwar

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

jarrad_murray
Contributor III
Contributor III
Author

Hi Sunny,

Please find attached a sample to test out.

Many thanks for your help with this.

sunny_talwar

Are you after a view like this?

Capture.PNG

jarrad_murray
Contributor III
Contributor III
Author

Ooh yes, that's it.

Is it the match function that is doing this?

Thank you very much.

sunny_talwar

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

jarrad_murray
Contributor III
Contributor III
Author

Brilliant,

Thank you so much for your help with this Sunny.