topic Re: Remove Nulls in Set analysis in QlikView

Remove Nulls in Set analysis

bharatkishore — Fri, 04 Mar 2016 10:50:49 GMT

I have two fields in db

1)LC2 Amount

2)Clrng Doc

For Ex:

Clrng Doc LC2Amount

111 76

222 85

Now my requirement is i want to calculate sum(LC2amount) which are having only clrng doc values. I don't want to calculate the LC2 amount for all Clrng Doc.

i have written an expression like this

=num(Sum({[Clrng doc]={"*"}>}[LC2 Amount]),'$ #,##0')

I am not sure whether my expression is correct or not.

Can you please let me know how to get only sum(LC2amount) which are having Clrng Doc Values

Thanks,

R .Bharat Kishore

Re: Remove Nulls in Set analysis

sunny_talwar — Fri, 04 Mar 2016 10:52:12 GMT

You are missing a < symbol, rest seems fine:

=Num(Sum({<[Clrng doc]={"*"}>}[LC2 Amount]),'$ #,##0')

Re: Remove Nulls in Set analysis

bharatkishore — Fri, 04 Mar 2016 10:58:05 GMT

Sorry Sunny that was just a copy paste mistake..Will the expression will satisfy my above requirement...

Re: Remove Nulls in Set analysis

sunny_talwar — Fri, 04 Mar 2016 10:59:45 GMT

If those are not white spaces, the result of the above expression should be 161

Re: Remove Nulls in Set analysis

bharatkishore — Fri, 04 Mar 2016 11:02:31 GMT

Yes i need exactly the same value...Can you write the necessary the expression.. i will just verify it.

Re: Remove Nulls in Set analysis

avinashelite — Fri, 04 Mar 2016 11:05:41 GMT

try like this

if( len(trim([Clrng Doc]))>0,sum([LC2 Amount]) )

Re: Remove Nulls in Set analysis

sunny_talwar — Fri, 04 Mar 2016 11:08:16 GMT

This seems to work:

=Num(Sum({<[Clrng Doc]={'*?'}>} LC2Amount),'$ #,##0')

Re: Remove Nulls in Set analysis

bharatkishore — Fri, 04 Mar 2016 11:12:09 GMT

Thanks for the post Sunny..May i know why we need to include "?" is it compulsory?

Re: Remove Nulls in Set analysis

sunny_talwar — Fri, 04 Mar 2016 11:14:55 GMT

No you don't have to, I was testing some else because you original code (which could have been because of the typo) mentioned Clrng doc (with lower case d) and the expression was not working. Later I realized the issue and wanted to revert back to the original expression, but forgot the ? in the there. So this works:

=Num(Sum({<[Clrng Doc]={'*'}>} LC2Amount),'$ #,##0')

Re: Remove Nulls in Set analysis

bharatkishore — Fri, 04 Mar 2016 11:22:44 GMT

Thanks Sunny!!!! I am getting clrng doc values from DB. So this will eliminate right.