14 Replies Latest reply: Oct 1, 2015 5:48 AM by Ruud Cruchten RSS

    Aggr costs

    Ruud Cruchten

      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
          Massimo Grossi

          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
            Camile Johnson

            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.

              • Re: Aggr costs
                Juan Olivares

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

                 

                02     190 /1 = 190

                  • Re: Aggr costs
                    Camile Johnson

                    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
                        Ruud Cruchten

                        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
                            Ruud Cruchten

                            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
                                Camile Johnson

                                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
                                    Juan Olivares

                                    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
                                        Camile Johnson

                                        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
                                            Ruud Cruchten

                                            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

                                                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
                                                    Ruud Cruchten

                                                    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

                                                    )