Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
nope, still returns nothing... Used it in the chart by the way.
Try to put your expressions in this order
Sum(if(Aggr(....)<=500,
Sum(Aggr(...
What about something like this (where 'Mutatieonderhoud' = 'Y' since that is simpler). Is this what your goal is:
See attached for more detail.
Ok... after this what is the result, because i see you get it.
02 190 /1 = 190
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.
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
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;
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