topic Re: Aggr costs in QlikView

Aggr costs

Wed, 23 Sep 2015 19:33:57 GMT

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

Re: Aggr costs

maxgro — Wed, 23 Sep 2015 19:42:54 GMT

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

Wed, 23 Sep 2015 20:15:47 GMT

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

Re: Aggr costs

jolivares — Wed, 23 Sep 2015 20:31:14 GMT

Try to put your expressions in this order

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

Sum(Aggr(...

Re: Aggr costs

cjohnson — Wed, 23 Sep 2015 20:52:30 GMT

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

jolivares — Wed, 23 Sep 2015 21:06:05 GMT

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

02 190 /1 = 190

Re: Aggr costs

cjohnson — Thu, 24 Sep 2015 02:22:31 GMT

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

Thu, 24 Sep 2015 05:52:23 GMT

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

Thu, 24 Sep 2015 05:55:33 GMT

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;

Re: Aggr costs

cjohnson — Thu, 24 Sep 2015 12:57:57 GMT

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

jolivares — Fri, 25 Sep 2015 13:21:45 GMT

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

cjohnson — Fri, 25 Sep 2015 13:36:06 GMT

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

Wed, 30 Sep 2015 14:59:27 GMT

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

Anonymous — Wed, 30 Sep 2015 21:14:40 GMT

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

Thu, 01 Oct 2015 07:11:00 GMT

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

)