How to ignore null in the expression

[ashis]

Hi,

In my data source I have column Parent1, Parent2,Parent3,Parent4 . The value of this column sometime null when there is no Parent .

I have one gantt chart where I am trying the following expression,

=aggr(max(Endtime)-min(Starttime),JOBNAME,Parent1,Parent2=,Parent3=,Parent4😃 (similarly I have 5 expressions)

It works fine if all the coulumns such as Parent1, Parent2,Parent3,Parent4 have value , but it does not work when there is null value for any of the Parent. It shows no data.

How to write expression such a way that it would ignore if parent is null.

Thank you,

[vishsaggi]

Can you share your app if possible to look into? When you say Null you mean you are getting - dashes or empty strings? May be you can try like below or you might have to write the expressions calculated dimensions.

= IF(Len(Trim(Parent1)) = 0 OR Len(Trim(Parent2)) = 0 OR Len(Trim(Parent3)) = 0 OR Len(Trim(Parent4) = 0, aggr(max(Endtime)-min(Starttime),JOBNAME,Parent1,Parent2,Parent3,Parent4))

OR try like

=aggr(max({<Parent1 = {"*"} ,Parent2 = {"*"},Parent3 = {"*"}, Parent4 = {"*"}>} Endtime) - min({<Parent1 = {"*"} ,Parent2 = {"*"},Parent3 = {"*"}, Parent4 = {"*"}>}Starttime),JOBNAME,Parent1,Parent2,Parent3,Parent4)

[niclaz79]

=aggr(max(Endtime)-min(Starttime),JOBNAME,Alt(Parent1,0),Alt(Parent2,0),Alt(Parent3,0),Alt(Parent4,0))

[ashis]

Hi , thank you for your reply.

I tried your expression however it did not work.

I did not try calculated dimension though.

[ashis]

Hi Niclas,

I want to aggregate if there is not null value. I tried your expression , but it did not work.

[ashis]

When I say null , after reloading the data in qlikview , I do not see any string ,it is blank in the straight table.

[vishsaggi]

Try in calculated dimension if not can you share your app with just that pivot table sheet?