Variables producing same result

[Anonymous]

I have a data set that looks something like this:

Employee	Earliest Event Time	Latest Event Time	Time Diff
Adam	08:01	16:00	07:59
Beatrice	08:04	16:07
Caroline	07:58	16: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?

[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]

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 · Accepted solution]

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]

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]

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]

This works, thank you very much.