Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
FerNava
New Contributor

Excluding values in expression

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

Labels (3)
1 Solution

Accepted Solutions
CarlosAMonroy
Contributor III

Re: Excluding values in expression

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

4 Replies
CarlosAMonroy
Contributor III

Re: Excluding values in expression

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

FerNava
New Contributor

Re: Excluding values in expression

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
Valued Contributor III

Re: Excluding values in expression

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

Channa
FerNava
New Contributor

Re: Excluding values in expression

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.