Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

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

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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

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:

2015-04-27 #5.PNG

View solution in original post

4 Replies
Highlighted
MVP
MVP

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

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:

2015-04-27 #5.PNG

View solution in original post

Highlighted
MVP
MVP

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

Slight error in Target2:

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

Highlighted
Not applicable

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

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.

Highlighted
MVP
MVP

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

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