topic Re: aggregate vales by relation from a diffrent dataset in QlikView

aggregate vales by relation from a diffrent dataset

Anonymous — Wed, 27 Jun 2018 12:34:56 GMT

Hi everyone,

I have some issues creating a table where an aggregated vale is not directly related to the grouping but the relation is defines in a diffrent set of the data. I tryed to solve the issue wir p() and e() functions in set analysis but couldnt find a solution jet.

I have a set of capacity hours per resource and date. Furthermore i have an assignment of resources to parts.

So data table looks like this.

Part	Res	Kap	Date
	1000	10	01.01.2000
	1000	15	02.01.2000
	1000	12	03.01.2000
	1001	14	01.01.2000
	1001	17	02.01.2000
	1001	15	03.01.2000
	1002	12	01.01.2000
	1002	13	02.01.2000
	1002	21	03.01.2000
A	1001
A	1002
B	1000
C	1000
C	1001
C	1002

Now i want want for each Part to show how nuch capacity the assigned resources have. So i need a sum over the capacity to get the following result.

Part	Kap
A	92
B	37
C	129
Total	129

Re: aggregate vales by relation from a diffrent dataset

Anonymous — Wed, 27 Jun 2018 12:40:41 GMT

I might need to make clear, tht the easy solution of just spliting the data table is not viable as the table is the cental facts table of a quite big datamodell and both fields part and resoucre are already key fields to other tables with lots of realtions.

Re: aggregate vales by relation from a diffrent dataset

sunny_talwar — Wed, 27 Jun 2018 15:53:18 GMT

Try this

=Sum(Aggr(Sum(TOTAL <Res> Kap), Res, Part))

Re: aggregate vales by relation from a diffrent dataset

Anonymous — Thu, 28 Jun 2018 13:25:48 GMT

Hi Sunny,

Tank you, thats not 100% what i wanted, as totals run in multiples and i had to tweak some set expressions to make it work with further selctions. But your input was the key step to find a working solution and therefore a great help to me.