Aggr issue

[#1]

I have the folowing dataset

MonthPart	Company	OrderID	Volume in KG	Volume in ltr
			15.819	31.122
1	Dummie	5113A1080555	2.090	4.111
1	Dummie	5174A1084003	305	600
1	Dummie	5175A1084027	441	868
1	Dummie	5180A1084257	305	600
1	Dummie	5187A1084647	362	712
1	Dummie	5187A1084701	1.252	2.464
1	Dummie	5187A1084715	293	577
1	Dummie	5187A1084720	610	1.200
1	Dummie	5188A1084740	483	950
1	Dummie	5189A1084780	483	950
1	Dummie	5195A1085169	525	1.033
2	Dummie	5191A1084782	2.927	5.758
2	Dummie	5197A1085316	3.304	6.500
2	Dummie	5201A1085458	2.439	4.799

Now i want a a aggr over the Orderid for the Volume in Ltr

So i can make the folowing calculation

If Volume in ltr for a order >5000 then volume /1000 *62.79 else volume /1000 *91.29

My result must be 2492 as total costs. But my aggr without the orderid dim doesn't work. Anybody an idea?

[sgrice]

So I take it the sum is by company month?

so is the sum of all orders volume for each company month?

[perumal_41 · Accepted solution]

Hi Drik.

please try below expression

Sum(Aggr(sum(if([Volume in ltr]>=5000, [Volume in ltr]/1000*62.79, [Volume in ltr]/1000*91.29)),OrderID))

[alexandros17]

This is the solution, let me know

[pokassov]

Why we need aggr?

[#6]

Allmost good. But there is one row who gives the wrong calculation

[perumal_41]

Hi Drik,

is possible give me scenarios .

Regards,

Perumal A

[#8]

This is my formula for now

Sum(

  Aggr(

  SUM(

  if(#Volume >5000, #Volume/1000*62.79, #Volume/1000*91.29))

  ,OrderID)

  )

It works on one exeption.

Is het possible to sum the volume before comparing it with 5000.

there is one order loaded on two tanks with dimmension cust_tank.

so he calculates the formule wrong because he thinks its a 2000 ltr drop and a 4500 drop instead of 6500.

Thanks a lot.

[perumal_41]

Hi Dirk,

Try below expression

Sum(

  Aggr(

  SUM(

  if(#Volume >5000, #Volume/1000*62.79, #Volume/1000*91.29))

  ,OrderID,cust_tank)

  )

[alexandros17]

Sorry,

did you take a look to my document?