Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Variables producing same result

I have a data set that looks something like this:

EmployeeEarliest Event TimeLatest Event TimeTime Diff
Adam08:0116:0007:59
Beatrice08:0416:07
Caroline07:5816:02

Where Time Diff is a function that looks like this:

=Interval( Max( EventTime ) - Min( EventTime ), 'hh:mm' )

I tried to put this formula into a variable, but it fails to compute. Similarly, if I put Max( EventTime) or Min( EventTime ) into a variable, it computes, but all values are the same. I expect that this is due to it calculating max and min for the entire data set rather than for each employee. Is there a way to create a variable that computes for each employee?

1 Solution

Accepted Solutions
sunny_talwar

Try setting your variable without the equal sign

Interval( Max( EventTime ) - Min( EventTime ), 'hh:mm' )

Also, read here:

The Little Equals Sign

The Magic of Variables

View solution in original post

9 Replies
its_anandrjs

In variable for which row you calculate the date difference why not you use this formula expression in any table.

Load

*,

Interval( [Clock Out Time] - [Clock In Time], 'hh:mm' ) as HourDiff

From Sorce;

Anonymous
Not applicable
Author

Thank you for your reply.

That looks like it would work. Is there a way to accomplish this without editing the data loader? I am hesitant to unlock my data loader.

sunny_talwar

Try setting your variable without the equal sign

Interval( Max( EventTime ) - Min( EventTime ), 'hh:mm' )

Also, read here:

The Little Equals Sign

The Magic of Variables

Anonymous
Not applicable
Author

Thank you for your detailed response. I have started reading the blog posts you linked. Getting rid of the equal sign solved my problem. For some reason, it does not work with my variable for [Earliest Event Time] which looks like this:

Min( Date( EventTime, 'MM/DD/YYYY h:mm' ) )

Without an equals sign, the equation shows up in the table as a string. With an equals sign, it calculates the min for the entire data set. However, after reading and experimentation I'm sure it will be resolved.

Thank you again.

sunny_talwar

You might need a dollar sign expansion of your variable here

$(VariableName)

sunny_talwar

You can read about dollar sign expansion here:

The Magic of Dollar Expansions

Anonymous
Not applicable
Author

This is excellent continued reading and seems to be the root of my problem. Thank you again for all of your help.

its_anandrjs

In your variable then write this

Interval( Max([Clock Out Time]) - Min([Clock In Time]), 'hh:mm' )

And also do not put equal sign before expression.

Anonymous
Not applicable
Author

This works, thank you very much.