topic Re: Associate Data in QlikView

Associate Data

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Associate Data

Peter_Cammaert — Mon, 05 Sep 2016 15:32:23 GMT

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

Re: Associate Data

richard_chilvers — Mon, 05 Sep 2016 15:40:38 GMT

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

Re: Associate Data

Wed, 07 Sep 2016 12:16:54 GMT

worked like a charm. Thx a lot!