topic aggr function in QlikView

aggr function

Fri, 07 Jul 2017 07:34:36 GMT

Hello,

I am back for another problem, that I couldn't find out.

For each "Code groupement", I have machines.

The machines are grouped by lines. For exemple:

on the image 1: machine Architect plus i2000sr and Immulite 2000xpi are on the same line.

I managed to have the technical time on 1 machine, per month. The formular used is :

sum(total<Machine,Month> aggr(sum([Nbr analyses]/[Cadence Chgt Tech])+sum([Nbr analyses]/[Cadence Manip Tech])+[Temps Set up annuel],Month,Machine))

But i wanted to display the total technical time of all machines which are on the same line.

So for example, both Architect plus i2000sr et Immulite 2000xpi have to display : 121,792xxxxx (which is 83,612xxx + 38,18xxxx)

I doubt to use the same formular above, but need to change a bit so that i can be grouped by lines.

Can anyone help me to find out?

Thanks very much

PS: I posted on the Qlik sense forum, but I repost in the Qlikview one, as I think there will be more people here... Thanks

Re: aggr function

sunny_talwar — Fri, 07 Jul 2017 11:20:24 GMT

Which field in your database tells you that they are on the same line? May be use this

Sum(TOTAL <Machine,Month, Line> Aggr(Sum([Nbr analyses]/[Cadence Chgt Tech]) + Sum([Nbr analyses]/[Cadence Manip Tech]) + [Temps Set up annuel], Month, Machine, Line))

Re: aggr function

Fri, 07 Jul 2017 13:03:44 GMT

Hi Sunny,

Thanks for your response.

Yes it is Line.

But the formular I posted above is not working anymore... I don't know why.

Never mind, I will start from the beginning.

So what I need to do for now, it's :

1. for the calculation 1, the formular used is :

(sum([Nbr analyses]/[Cadence Chgt Tech])

+sum([Nbr analyses]/[Cadence Manip Tech]))

For each Machine, in each "Code Groupement", I have the result as in image 1. You can see that the Machine Architect plus i2000sr is found in Code groupement Arnaud and Atout Bio and so on.

2. the calculation 1 needs to be grouped Machine to have the calculation 2

So for the month 2, machine Architect plus i2000sr, the calculation 2 will be : 0,73 + 0,17+ .... so on (image 2)

3. The calculation 2 will be added by the "Technical Annual Time" which is 37,5 for the machine Architect. So for example, if the result of the calculation 2 is 46,11, the calculation 3's result is : 46,11 + 37,5 = 83,61. Then you have the calculation 3.

Update : I found the formular for the calculation 3 :

sum(total<Mois,Machine,[Code Groupement]> aggr(sum(total<Machine,Mois>aggr((sum([Nbr analyses]/[Cadence Chgt Tech])

+sum([Nbr analyses]/[Cadence Manip Tech])),Machine,Mois))+[Temps Set up annuel],Mois,Machine,[Code Groupement]))

4. You know that the Machine Architect plus i2000sr is on the same line as Immulite 2000xpi.

So the calculation 3 is the calculation 3 of the machine Architect + the calculation 3 of the machine Immulite. Then you have : 83,61 + 38,19 = 121,8. Then you have the calculation 4.

5. you will take 83,61 / 121,8*100 to have the calculation 5 for the machine Architect

and 38,19/121,8*100 to have the calculation 5 for the machine Immulite.

Can you help me to display the calculation 5's result for each machine on each month then? Of course, the result must be displayed the same on the machine Architect of the group Anaud, and of the group Atout Bio

Thank you very much