Filtering a pivot table to show only the "true" values

[#1]

I am finding it very easy to feel stupid as a newbie to QlikView.  With respect to Qlik because it is a great application, the documentation is not the most user friendly thing out there and while the forum is great, it can be frustrating to try and find the exact thing you need to accomplish something.

I would think this should be easy but I cannot find the answer.

I have created a small table to look at the number of patients, costs, and ER visits in patients with Ovarian CA.  One of the things I have created in the underlying data is a flag in the claims file that indicates whether the services were DIRECTLY related to the underlying disease (Ovarian cancer) using an algorithm and a 'Y' if true and null if not.

I can create the output I am looking for in a pivot table.

Service Category	Patients	Allowed$	ER Visits
0	7849	295849572.84	6155
-1	365	578606.45	670

These numbers are all correct when run in SQL Server.  The 0 is the false state which is running all of the data with no filtering (we call this "all cause".  The -1 is the true state where the data has been filtered to include only those er services and costs that are directly attributable to this disease.

But,  all I want to show is the -1 data andit actually should be showing the field name which is "ER/Urgent Care"

I have tried every which way to do this but cannot find the right way.  Is there a reasonably easy way to suppress the 0 data values?  Supressing zeroes does not remove that data/

I can live with the minus 1 and create an alternate name for it but I do not want both rows of this data in this particular table.

Thanks for any help/suggestions.

Steve

[#2]

You can create a calculated dimension for your Dimension to suppress the values based of your expression in the Dimension.

IF([Service Category] = -1 , [Service Category] , Null() ) and Select Suppress When Value is Null option.

The other way is you can filter out the data on your Expression as well with Set Analysis.

Count({<[Service Category]={-1}>} PATINET_ID)

If you don't need the 0 data on your report, simply filter out the data while loading because unused data made problems on the UI.

[maxgro]

if Service Category is your dimension, maybe with a calculated dimension

if([Service Category]=0, null(), 'alternate name')

and check suppress when value is null

[consenit]

Hi there.

I agree with the suggestions posted. However, keep in mind calculated dimensions using IF() tend to be less performant on large datasets than Set Analysis.

Kind regards,

Ernesto.

[#5]

Thanks to all of you for responding.  I will definitely try all of these but the dimensions is already a calculated dimension so I will need to add these additional criteria.  The current dimension is an If statement that includes both Disease_Specific='Y' and Service_Category = 'ER/Urgent Care'.

Can you include what u suggested in a conditional statement or can t be appended to the If statement?  I could not find if there is a limit to how many criteria an If statement can handle.  Would adding an AND make more sense?

Like I said.  Really like the tool but do not have the syntax down.....yet.

Thanks again to Dathu, Maxgro and Ernesto.

[jaspsing]

Hi,

I hope this will work for you, use Set Analysis to filter out the data in front end in case you still want you document to carry all the data.

Thanks

JS

[#7]

Correct Answer!

Thanks Jaspal.  This and a short video "A Beginners Guide to Set Analysis"  did the trick.  Once I understood the sequence and saw the syntax needed, it clicked and is working fine.

Thanks to you all.