Can any one please do needful on Getting Exact Values of Total in Text box eliminating Nulls.

[#1]

Hi friends,since last couple of days facing an Issue with handling of null and to eliminate the complete row where ever value is null in Dimension drop the complete row.My Question is simple and straight i have a measure with Target while showing in St Table for Individual dimension all values are matching and total i'm getting is Avg=23.26%,but  expected 21.5% (to get these need to eliminate null value rows Excel 52 and 56 records has null value of Target)certain columns having null values.when ever it is null i need to eliminate complete row in calculations >?I have attached Excel source and made how i need to get the desired output.Please help on these resolved.

[petter · Accepted solution]

To eliminate the NULL's for TARGET you will have to use a set expression in each of your sums for GP and Derived:

{ <TARGET={'*'}> }

And you will get this result:

[petter]

Slight error in Target2:

It should be      Derived2/GP2-1        ( not 1-Derived/GP2 )

[#4]

Thanks for your help and Sorry i have one Question Is it Possible to get Elimination Of Null

Sum( {<TARGET={*}>} GP) in Script Level it self.If not no probem thanks for yor Answer.

[petter]

It should be just as easy to do it on a script level.

LOAD

          F1,

          F2

FROM

       ABC.XLSX (....)

WHERE

       F2 <> '' AND F1 <> '';

Excel doesn't have regular Null-values as such but they might be interpreted as such during the LOAD process in a load script. So I can't remember on the fly whether you will have to test for empty strings like I did above or if QlikView translates them into Null-values ....

LOAD

          F1,

          F2

FROM

       ABC.XLSX (....)

WHERE

       Not(IsNull(F2)) AND Not(IsNull(F1));