Excluding values in expression

[FernandaNava]

Hello, 

I'm calculating time passed between two dates like this:

floor(avg(aggr(Date#(EndDate'YYYYMMDDhhmmss') - Date#(StartDate'YYYYMMDDhhmmss'),IDEsame))*24*60)

I'm getting a weird (negative) value for two reasons:

1. Start date sometimes (because of a user error) is 0

2. End date sometimes is null because the process is not finished

I'm trying to create an expression for this calculation without taking into account the aforementioned cases. I cannot exclude on load or otherwise because I still need those records for other things.

I tried:

if(StartDate<>0 and EndDate <>null, (floor(avg(aggr(Date#(EndDate'YYYYMMDDhhmmss') - Date#(StartDate'YYYYMMDDhhmmss'),IDEsame))*24*60)))

but it returns a null value

Thanks for your help

[CarlosAMonroy · Accepted solution]

Hi,

There are many ways to solve this, You can try flagging those cases in the script, then just use the flag in the expression.

Something like this:

Load *,

if(StartDate = 0 or isnull(EndDate),1,0) as FlagExcludeDates

From TableName;

Then your expression should look like this:

 floor(avg({<FlagExcludeDates-={1}>}aggr(Date#(EndDate'YYYYMMDDhhmmss') - Date#(StartDate'YYYYMMDDhhmmss'),IDEsame))*24*60)

Hope that helps,

Carlos M

[FernandaNava]

Thank you!

While trying this I realized my variable EndDate is actually a string, so I used Len(EndDate)<1 as a condition instead. I can see in the tables that the flag is generating correctly, but I still get a null value for my expression. 

Why is the set analyisis exlusion "{<FlagExcludeDates-={1}>}" placed in that position?

[Channa]

if(isnull(StartDate) OR Len(Trim(StartDate)) = 0 

[FernandaNava]

Thank you, this worked! I didn't know you can't do the calculation inside the master dimension, but for the chart expression it worked.