topic Re: Remove Nulls from a Summary Table in QlikView

Remove Nulls from a Summary Table

Tue, 16 Apr 2013 19:53:24 GMT

I'm trying to get a summary table set up so I can set up multiple charts

Issue I'm having is with this formula:

sum(Aggr([Working Days],[Employee Name],[Activity Date],[Start Date],[Job Title]))

My total results should be 726 - removing nulls from the Start Date. If I have the Start Date Dimension, it works fine. But in the Summary table with no dimensions, I get 728. Testing the data, I have 2 rows with no Start Date. I tried doing an extension of the expression (IsNull[Start Date])), expression) but get a 0 in return.

What else can I try?

Thanks.

Re: Remove Nulls from a Summary Table

Tue, 16 Apr 2013 19:57:50 GMT

Try

if(IsNull[Start Date]<>0,sum(Aggr([Working Days],[Employee Name],[Activity Date],[Start Date],[Job Title])))

Rebeca

Re: Remove Nulls from a Summary Table

Tue, 16 Apr 2013 20:04:17 GMT

I tried that and it comes back with a 0.

Re: Remove Nulls from a Summary Table

spividori — Tue, 16 Apr 2013 20:05:55 GMT

Hi.

With the following test:

sum(if(num([Start Date])>0,Aggr([Working Days],[Employee Name],[Activity Date],[Start Date],[Job Title])))

Could you upload a sample to test.

Regards.

Re: Remove Nulls from a Summary Table

Tue, 16 Apr 2013 20:12:39 GMT

Perfect. Thanks!!