Remove Nulls in Set analysis

[bharatkishore]

Hi

I have two fields in db

1)LC2 Amount

2)Clrng Doc

For Ex:

Clrng Doc    LC2Amount

111               76

222               85

                    48

                     50

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

[sunny_talwar]

You are missing a < symbol, rest seems fine:

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

[bharatkishore]

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

[sunny_talwar]

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

[bharatkishore]

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

[avinashelite]

try like this

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

[sunny_talwar]

This seems to work:

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

[bharatkishore]

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

[sunny_talwar · Accepted solution]

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')

[bharatkishore]

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