topic Sum?, aggr? Both? Don't Know in QlikView

Sum?, aggr? Both? Don't Know

Thu, 07 Jul 2011 08:49:40 GMT

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:

So the straight table should be like this:

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:

The final Table should be:

Thanks in advance!!

Sum?, aggr? Both? Don't Know

brenner_martina — Thu, 07 Jul 2011 09:00:15 GMT

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

kaushiknsolanki — Thu, 07 Jul 2011 09:01:25 GMT

Hi,

Can you please upload the qvw file.

Regards,

Kaushik Solanki

Sum?, aggr? Both? Don't Know

Miguel_Angel_Baeyens — Thu, 07 Jul 2011 09:06:48 GMT

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

Thu, 07 Jul 2011 10:55:34 GMT

Here you are. Thanks for interest!

Sum?, aggr? Both? Don't Know

brenner_martina — Thu, 07 Jul 2011 11:00:42 GMT

Hi, Pedro,

you tables has no connection. You have to join them by IDs, minimum Unqualify CalendarDate;

Sum?, aggr? Both? Don't Know

kaushiknsolanki — Thu, 07 Jul 2011 11:11:30 GMT

HI,

I would say there should be some linking in the tables you have used in application.

So decide the field which is common in both the tables and link the tables using that.

By linking you will easily get the Ripple with respect to Item.

Regards,

Kaushik Solanki

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

Thu, 14 Jul 2011 07:52:28 GMT

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.