Aggr costs

[#1]

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))

, ' ')

And also a load statement:

LOAD

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

[jolivares]

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.

[cjohnson]

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)....

[#4]

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)

[Anonymous]

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?

[#6]

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

)