Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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

7 Replies
brenner_martina
Valued Contributor II

Sum?, aggr? Both? Don't Know

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

Hi,

   Can you please upload the qvw file.

Regards,

Kaushik Solanki

MVP
MVP

Sum?, aggr? Both? Don't Know

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

Not applicable

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

Here you are. Thanks for interest!

brenner_martina
Valued Contributor II

Sum?, aggr? Both? Don't Know

Hi, Pedro,

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

Sum?, aggr? Both? Don't Know

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

Not applicable

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.

Community Browser