14 Replies Latest reply: Oct 1, 2015 5:48 AM by Ruud Cruchten

# Aggr costs

Hi,

I have to aggr the sum of three fields per Key field and perform a check if it <500. Then divide it per unique key. ONLY for costs with ordersoort='Mutatieonderhoud'

Key, Costs A, Costs B, Costs C

01, 300, 100, 200

02, 10, 50, 50

02, 50, 0, 30

03, 600, -300,150

03, -100, 50, 50

The check would return:

02 = 190

03 = 150

I've tried:

IF( SUM(

Aggr(

(SUM( {<Ordersoort={'Mutatieonderhoud'}>} [Kosten totaal excl. BTW]) -

SUM( {<Ordersoort={'Mutatieonderhoud'}>} [Activa bedrag]) -

SUM( {<Ordersoort={'Mutatieonderhoud'}>} [Bedrag betaler NPO])

), VHE_ID))<=500,

SUM(

Aggr(

(SUM( {<Ordersoort={'Mutatieonderhoud'}>} [Kosten totaal excl. BTW]) -

SUM( {<Ordersoort={'Mutatieonderhoud'}>} [Activa bedrag]) -

SUM( {<Ordersoort={'Mutatieonderhoud'}>} [Bedrag betaler NPO])

), VHE_ID))   / COUNT(DISTINCT(VHE_ID))

, ' ')

VHE_ID,

IF( Ordersoort='Mutatieonderhoud',

SUM([Kosten totaal excl. BTW]) - SUM([Activa bedrag]) - SUM([Bedrag betaler NPO]), null() ) as [Kosten excl. BTW - Act bedr. - Bedr. bet. MUTATIEONDERHOUD PER VHE],

FROM

\$\$\$\$\$\$.qvd

(qvd)

Group By VHE_ID, Ordersoort;

Stil can't get the correct numbers...

Thank for you help

• ###### Re: Aggr costs

maybe (you have to add the set analysis in the sum

dimension

Key

expression

if(rangesum(sum([Costs A]),sum([Costs B]), sum([Costs C]))<=500,

rangesum(sum([Costs A]),sum([Costs B]), sum([Costs C])) / count(Key))

• ###### Re: Aggr costs

nope, still returns nothing... Used it in the chart by the way.

• ###### Re: Aggr costs

Try to put your expressions in this order

Sum(if(Aggr(....)<=500,

Sum(Aggr(...

• ###### Re: Aggr costs

What about something like this (where 'Mutatieonderhoud' = 'Y' since that is simpler). Is this what your goal is:

See attached for more detail.

• ###### Re: Aggr costs

Ok... after this what is the result, because i see you get it.

02     190 /1 = 190

• ###### Re: Aggr costs

I think that is what his goal is..To only divide by the total number of distinct keys if Ordersort is equal to the appropriate value. From his example I made the assumption that the Key 03 has an Ordersort value of Mutatieonderhoud since his output indicated that. If that is not correct then let me know and I'll see if I can make modifications accordingly.

• ###### Re: Aggr costs

Oke maybe I've simplified the example to much, but in reality there can be more costs (e.g. records) with the same key. The goal is to sum all these costs (with ordersoort=mutatieonderhoud) per key (so in the example the costs of both records with key 02) and then evaluate if these total mutatieonderhoud costs per vhe are <500. That's why I think the only way of achieving this is an Aggr, since a SUM will evaluate each record on <500, not the total costs per KEY

• ###### Re: Aggr costs

Last update:

Tried another load statement, also gives the wrong values:

VHE_ID,

Ordersoort,

SUM([Costs A) + SUM([COsts B]) + SUM([COsts C), null() ) as [Total kosts per VHE],

FROM

\$\$\$\$\$.wvd

(qvd)

WHERE Ordersoort = 'Mutatieonderhoud'

Group By VHE_ID, Ordersoort;

• ###### Re: Aggr costs

In your original example can you indicate the records that have an Ordersort value of 'Mutatieonderhoud'? If you could just add an "Ordersort" column below that would be great:

Key, Costs A, Costs B, Costs C

01, 300, 100, 200

02, 10, 50, 50

02, 50, 0, 30

03, 600, -300,150

03, -100, 50, 50

The check would return:

02 = 190

03 = 150

Thanks,

Camile

• ###### Re: Aggr costs

I recommend to you to be more clear with your require...  Try to make a excel worksheet in order to explain exactly what you need.

• ###### Re: Aggr costs

That is exactly what I am requesting of Ruud (OP)...I'm having trouble understanding what the actual requirement is - so yes it would be great if you (Ruud) could mock-up a worksheet (or inline table) showing exactly what you are looking for (as I indicated above)....

• ###### Re: Aggr costs

Okay so to be clear, each unique Key is a house id.  My clients wants to see the total costs per house and make a divide on these total costs into two categories: costs <500 and costs >500.

The end result should be two tables:

Table 1 Sum of Costs <500 per house

Table 2 Sum of Costs >500 per house

It is important that the costs are aggregated before the <500 or >500 classification takes place since the costs of maintenance of a house throughout a year is made up of x number of smaller amounts. Simply checking each field on <500 will result in a wrong classification since that will check each record on <500.

For example

House id, costs, expln

1     50      paint

1     20       door fixed

2     300     new kitchen door

2     50       paint

2     250     floor cleaning

Total costs of house 1= 70, so <500   -> table 1

Total costs of house 2= 600, so >500 -> table 2

Hope this clarifies. Due to legal issues I can't share any data.

This is a screenshot of the table (with the wrong calculation) as it should look like:

Ordersoort = Type of maintenance

Aannemer = Contractor

The third column is the sum of costs (per address the costs that are >500)

• ###### Re: Aggr costs

I think that part is clear to me (looking for house ID > 500 and house ID sum < 500-- what i don't understand is the Ordersort/Type of maintenance = Mutatieonderhoud. How does that fit into your calculation? Can you add that as a column in your data set and then show the desired output?

• ###### Re: Aggr costs

This is a sample of actual data:

=house id           =type of maintenance     =Costs

VHE-nummer    Ordersoort                      Kosten excl. VAT      Costs ...   Costs...

11336100283    Mutatieonderhoud           € 3.074,30               xxxx          xxx

11336100283    Individueel PO (B/K/T)    € 2.073,97               xxx            xxx

11336100283    Mutatieonderhoud           € 166,95

11336100283    Klachtenonderhoud         € 641,31

The dimension of the table is Ordersoort (type of maintenance)

The expression that is currently used:

IF((Aggr((SUM([Kosten excl. VAT]) + SUM(Costs...]) + SUM([Costs...])), VHE_ID))<=500,

(Aggr((SUM([Kosten excl. VAT]) + SUM(Costs...]) + SUM([Costs...])), VHE_ID) / COUNT(DISTINCT(VHE_ID)),

0

)