7 Replies Latest reply: Jul 14, 2011 3:52 AM by Pedro Burgo RSS

    Sum?, aggr? Both? Don't Know

      Hi everyone, hope somebody can lead me. I want to create a Straight Table with a dimension (Item) and several Expressions (Quantity, Sales, Rappel). The Data are like as follows:

      Captura.JPG

       

       

      So the straight table  should be like this:

      Captura.JPG

      The problem comes with the Expression Rappel. The values of Rappel come from another table and refer only to the Customer, not to the Item. So If the customers have the following Rappel:

      Captura.JPG

       

      The final Table should be:

      Captura.JPG

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Thanks in advance!!

        • Sum?, aggr? Both? Don't Know
          Martina Brenner

          Hi, Pedro,

           

          I think you have a problem by join your tables. QlikView can not know, which Rappel belongs to which Item. Do you have an example for us?

          • Sum?, aggr? Both? Don't Know
            Kaushik Solanki

            Hi,

             

               Can you please upload the qvw file.

             

            Regards,

            Kaushik Solanki

            • Sum?, aggr? Both? Don't Know
              Miguel Angel Baeyens de Arce

              Hi,

               

              It all depends on the criteria to get the right results on the Rappel. How these amounts should be splitted?

               

              Regards.

               

              Miguel Angel Baeyens

              BI Consultant

              Comex Grupo Ibérica

              • Re: Sum?, aggr? Both? Don't Know

                Here you are. Thanks for interest!

                • Re: Sum?, aggr? Both? Don't Know

                  Hi and thank you all for your help. First I must apologize but I couldn't connect these days. Finally I've found a way to achieve what I wanted. I didn't have to connect the tables

                   

                  //Customer A has its rappels in the Account Nº 7090002
                    (Sum(  {<CalendarDate={'<=$(=Date(vFFG)) >=$(=Date(vFIG))'} , Gestion.CodigoCliente={CustomerA} >} Gestion.Qty) / Sum( total {<CalendarDate={'<=$(=Date(vFFG)) >=$(=Date(vFIG))'},Gestion.CodigoCliente={CustomerA}>} Gestion.Qty))
                  * Sum(total {<CalendarDate={'<=$(=Date(vFFG)) >=$(=Date(vFIG))'} , OutGestionTotal.NCuenta={7090002} >} OutGestionTotal.ImporteCompra)
                  +
                  //Customer B has its rappels in the Account Nº 7090003
                    (Sum(  {<CalendarDate={'<=$(=Date(vFFG)) >=$(=Date(vFIG))'} , Gestion.CodigoCliente={CustomerB} >} Gestion.Qty) / Sum( total {<CalendarDate={'<=$(=Date(vFFG)) >=$(=Date(vFIG))'},Gestion.CodigoCliente={CustomerB}>} Gestion.Qty))
                  * Sum(total {<CalendarDate={'<=$(=Date(vFFG)) >=$(=Date(vFIG))'} , OutGestionTotal.NCuenta={7090003} >} OutGestionTotal.ImporteCompra)
                  +
                  //Customer C has its rappels in the Account Nº 7090004
                    (Sum(  {<CalendarDate={'<=$(=Date(vFFG)) >=$(=Date(vFIG))'} , Gestion.CodigoCliente={CustomerC} >} Gestion.Qty) / Sum( total {<CalendarDate={'<=$(=Date(vFFG)) >=$(=Date(vFIG))'},Gestion.CodigoCliente={CustomerC}>} Gestion.Qty))
                  * Sum(total {<CalendarDate={'<=$(=Date(vFFG)) >=$(=Date(vFIG))'} , OutGestionTotal.NCuenta={7090004} >} OutGestionTotal.ImporteCompra)
                  
                  

                  The problem of joining tables is that one of these tables has the lines of the delivery notes and the movement of the items and the other has the accounts amounts. In the account tables, the amount of rappels are registered at the end of the month for the total amount of the month, so I would have to get first the proportional part to every line of the delivery. Besides there were only some specific customers with this problem.

                  Thanks again.