Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

, ' ')

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

14 Replies
maxgro
Not applicable

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

Not applicable

Re: Aggr costs

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

jolivares
Not applicable

Re: Aggr costs

Try to put your expressions in this order

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

Sum(Aggr(...

cjohnson
Not applicable

Re: Aggr costs

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

image21.png

image22.png

See attached for more detail.

jolivares
Not applicable

Re: Aggr costs

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

02     190 /1 = 190

cjohnson
Not applicable

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.

Not applicable

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

Not applicable

Re: Aggr costs

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
Not applicable

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