Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Did you mean:
Creator III

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
Master II

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
Anonymous
Not applicable

Hi,

Provide with the sample data

Creator III
Author

Hi Aruna,

Partner - Creator III

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

Creator III
Author

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 - Creator III

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

Creator III
Author

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 - Creator III

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

Master II

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

Creator III
Author

Hi Frank,

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

Community Browser