Associate Data

[#1]

Hello all,

I'm new to Qlikview and building just a basic understanding using it.

I have a challenge summing up some time Data.

I'm looking at the following information. A location, a username, a date and daily timesums.

Now I want to see the sum of the hours for all hours a user is over 75 hours a month.

User          Hrs

Max          83

Sammy     81

Goofy        61

should Result in 14 hrs.

If I create a new table box and select the month, location und username  username as dimensions and create expression that looks like this:

If(sum([015_Hours]) >75, Sum([015_Hours]))-75

I will get a list with all usernames and the correctly calculated "overtime" But I finally need the sum of all users of one location as an expression in another table.

If I simply add the same expression from above in a table which doesn't include the username as a dimension it will simply return the Sum of 015_hours as it will always be over 75 without the username correlation.

Is there a way to do this with a well formulated expression or do I need to calculate some sums already on the import to be able to do this?

thanks in advance.

Rob

[Peter_Cammaert]

You can use the aggr function, like this

=sum(aggr(If(sum([015_Hours]) >75, Sum([015_Hours]))-75, username, month, location))

Aggr will create a virtual table of the same expression results by username, month, location, to be used in another table context.

You may have to add further dimensions if they are used in your new object

[richard_chilvers]

Hi Rob

You should be able to do almost anything you want with your data in the front end. But you might also like to think doing some of the calculations as part of your script, probably during the LOAD. In this case, perhaps calculate a new field such as ExcessOvertime for hours over 75. This will make the front end design more intuitive for you and others.

HTH

[#4]

worked like a charm. Thx a lot!