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

[maxgro]

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

[#3]

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

[jolivares]

Try to put your expressions in this order

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

Sum(Aggr(...

[cjohnson]

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

See attached for more detail.

[jolivares]

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

02     190 /1 = 190

[cjohnson]

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.

[#8]

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

[#9]

Last update:

Tried another load statement, also gives the wrong values:

LOAD

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;

[cjohnson]

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