Exclude blank/null from straight table

Mon, 25 Apr 2016 15:28:12 GMT

I have a data set with a column for Name_of_Covered_Drug and a column for the Total_Spend associated to that drug. There is some spend reported that is not associated to a drug so the Name_of_Covered_Drug can be blank. I want a table showing top 10 drugs by spend but my table keeps including a single bar grouping all the blank drug names. I tried "Suppress when value is null" in Dimensions and I also tried the expression of

Sum ({$<Name_of_Covered_Drug-={NULL}>}Total_Spend)

also Sum ({$<Name_of_Covered_Drug={"*"}>} Total_Spend)

Any advice on how to type expression so that it will give me Total Spend only when a drug name is present(excluding nulls)?

Thanks

Re: Exclude blank/null from straight table

sunny_talwar — Mon, 25 Apr 2016 15:51:41 GMT

Can you try this as your dimension:

If(Len(Trim(Name_of_Covered_Drug)) > 0, Name_of_Covered_Drug)

and now select 'Suppress When Value Is Null' on the dimensions tab

Re: Exclude blank/null from straight table

Tue, 26 Apr 2016 00:45:46 GMT

Perfect! Thank you

topic Exclude blank/null from straight table in QlikView

Exclude blank/null from straight table

Re: Exclude blank/null from straight table

Re: Exclude blank/null from straight table