how to exclude null values in straight table

[bvssudhakar]

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

Please help me out to achieve this

Thank you in advance

[Anonymous]

Hi,

Provide with the sample data

[bvssudhakar]

Hi Aruna,

Please find the attachment

[raman_rastogi]

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

[bvssudhakar]

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

Thank you in advance

[raman_rastogi]

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

Try this In Your expression

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

Regards

Raman

[bvssudhakar]

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

[raman_rastogi]

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

Try this in your expression

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

And change Total mode to sum of rows.

Regards

Raman Rastogi

[Frank_Hartmann · Accepted solution]

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

[bvssudhakar]

Hi Frank,

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