# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Contributor II

## how to exclude null values in straight table

Hi Experts,

I have created a straight table with following dimensions and expressions.

Dimensions: Department,Title Name,Skill Name,Enter Date, jobid.

Expression 1:   =Count(DISTINCT{<Recruiter={'Irfan Memon','Rajiv Ranjan','Aditi Saxena','Sneha Singh'},JobClassid={4}>}jobid)

Expression 2:  =Sum(Aggr(Sum(DISTINCT {<Recruiter = {'Irfan Memon', 'Rajiv Ranjan', 'Aditi Saxena', 'Sneha Singh'}, JobClassid = {4}, Date_Flag={'EnterDate'}>} NoOpenings), jobid))

For these dimensions and expression my chart has correct output see below

Now I added one more column,Expression is: =fabs(Interval(EnterDate - Today(),'d')).

When i added the column with this expression all null values are also coming. Why i am not understanding. See this image

1 Solution

Accepted Solutions
Honored Contributor II

## Re: how to exclude null values in straight table

you can try leaving your fabs formula as expression (and change Total mode to sum of rows)

but change your first dimension to this:

=if((Aggr(Sum(DISTINCT {<Recruiter = {'Irfan Memon', 'Rajiv Ranjan', 'Aditi Saxena', 'Sneha Singh'}, JobClassid = {4}, Date_Flag={'EnterDate'}>} NoOpenings), jobid))>0,If(Len(Trim(Department)) > 0, Department),Null())

And supress Null Values for this first dimension

9 Replies
Partner

## Re: how to exclude null values in straight table

Hi,

Provide with the sample data

Contributor II

Hi Aruna,

Partner

## Re: how to exclude null values in straight table

Hi Sudhakar

Here in your expression 1 and 2 you are grouping your joibId and NoOpenings and in 3rd expression your calculating intreval in detail level.

fabs(Interval(EnterDate - Today(),'d'))

Instead of expression make it  calculated dimension.

share sample data or app to look more

Regards

Raman

Contributor II

## Re: how to exclude null values in straight table

Hi Raman,

I have changed that expression as a dimension now it's working perfectly. But i have one small query

How can i show the total for that calculate column

Partner

## Re: how to exclude null values in straight table

As per my knowlege there is No option are avilable to show total of dimension you can try some work around menstion in below link

Total with dimension value

Or

fabs(Interval(max(EnterDate) - Today(),'d'))

Regards

Raman

Contributor II

## Re: how to exclude null values in straight table

Hi Raman,

It's not working. it is giving eeror in calculated dimension

And

I am using personal edition. So I can't open those files in that thread.

Is there any possible way?? Please suggest

Thank you

Partner

## Re: how to exclude null values in straight table

In your data model there are some jobid which are not mapped with departments (shown in table chart)

If you do not want to show null value in your table you can suppress null values for Department dimension.

As we can not show total of dimension values ,if you want to show total you have to make expression

(Interval( Today()-max(EnterDate),'d'))

And change Total mode to sum of rows.

Regards

Raman Rastogi

Honored Contributor II

## Re: how to exclude null values in straight table

you can try leaving your fabs formula as expression (and change Total mode to sum of rows)

but change your first dimension to this:

=if((Aggr(Sum(DISTINCT {<Recruiter = {'Irfan Memon', 'Rajiv Ranjan', 'Aditi Saxena', 'Sneha Singh'}, JobClassid = {4}, Date_Flag={'EnterDate'}>} NoOpenings), jobid))>0,If(Len(Trim(Department)) > 0, Department),Null())

And supress Null Values for this first dimension

Contributor II

## Re: how to exclude null values in straight table

Hi Frank,

It's working perfectly. Thank you, Thank you so much for your support