0

SUM wih calculation in each number....

Pablo Álvarez

June 30, 2019 14:04

Answered

Follow

Hello, this is a tough question.  I want to get the sum of quantities of a family (grouped in a dimension) sold in a period, that is easy (sorry if name of tables are wrong, I use spanish version so I just put a translation as reference):

=NL("Sum";"Invoice sales";"Quantity (base)";"Global Dimension 1";"ABC001";"Date";"010119..311219")

On the other hand, suppose that product is a rope and I want to know how many meter I sold, each Nº has a different length, and I have all length in a predet. Dimension table, so if I want the length of a single Nº it is something like this:

=NL(;"Dimension predet.";"Cod. value dimension";"Cod. dimension";"LENGTH";"Nº";"REF")

So now I need to make the SUM of all lines in the invoice line table BUT IN METERS.  That means it has to multiply the Quantity on the first formula by the result on the second one for that Nº.

I hope you can understand what I'm trying to do....  please help!!

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Harry Lewis July 01, 2019 14:44

  Hello Pablo -

  Using the translated names you list, you would want to use a technique similar to this:

  =NL("Sum","Invoice Sales","=NF(,""Quantity (base)"")*NL(""First"",""Dimension predet."",""Cod. value dimension"",""Cod. dimension"",""LENGTH"",""No."",""REF"")","Global Dimension 1","ABC001","Date";"010119..311219")

  Based on the filters for Global Dimension 1 and Date, this will take the each Quantity (base) from the Invoice Sales table, multiply that value by the first Cod. value dimension value in the Dimension Predet table, and sum up all the results.

  I hope that helps.

• 0
  
  Pablo Álvarez July 02, 2019 18:45

  Thank you for our help!!!

  I'm trying to implement but I don't get the right formula.  This is my try:

  =NL("Suma";"Histórico lín. factura venta";"=NF(,""Quantity"")*NL(;""Dimensión predet."";""Cód. valor dimensión"";""Cód. dimensión"";""LENGTH"";""Nº"";NF(;""Nº""))";"Cód.Dim";$D23;"Dealer nº";$C23;"Date";$D$16)

   

  Probably problem is on the NF nested on the NL.  I'm trying to get the value in the predet. dimension from the product nº from the invoice line....

  Something is wrong there... but I'm not sure what is wrong...

• 0
  
  Harry Lewis July 02, 2019 21:32

  Hello Pablo.

  What trouble are you having?

  At a glance, the only issues I see are the comma in NF(,""Quantity"") - which should be a semicolon - and should there be a space in this field name: Cód.Dim ?

• 0
  
  Pablo Álvarez July 02, 2019 21:34

  Harry THANK YOU!!! I checked the formula and problem was the comma...... Thank you for your help!!! It works perfectly now!!!

  You're the best!