Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Partner - Specialist II
Partner - Specialist II

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?

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

   Can you please upload the qvw file.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Miguel_Angel_Baeyens

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
Author

Here you are. Thanks for interest!

brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi, Pedro,

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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.