Sum range date problem

[#1]

Hi everyone, I have a simple table with one dimension (CustomerCode) and several expressions. This table shows the sum of Nº BOxes, Kg, Sales etc of the delivery notes grouped by Customer, between two dates set by 2 variables vFIG (First Date) and vFFG (Last Date). I'm trying to set a new expressions which must show the sum of the amounts of a special Account between these two dates. The expression is like this

IF(Mngnt.CustomerCode='CNA008020',SUM( {<PostingDate={'<=(=Date(vFFG))
 >=$(=Date(vFIG))'}>}If(Table1.AccountNumber='7090002',Table1.Amount,0)),0)

The problem comes with the dates. Let's suppose I set the date range 2011/01/01-2011/01/31. I have delivery notes oh that customer until the 2011/01/20. If an amount of the 7090002 account number was register on 2011/01/21 it doesn't show. It only shows the sums of the account registered before the last date of the delivery note date.

With numbers:

Last Delivery note Date: 2011/01/20

Accounts amounts: 1000 (2011/01/15), 1000 (2011/01/18), 1000 (2011/01/21)

The expression only shows 2000. but if a I create a new delivery note on 2011/01/21 then it will show 3000. Is there any way to make it show what I want regardless of the customers delivery dates, Just taking the variables dates.

Thanks in advance.

[Miguel_Angel_Baeyens]

Hello Pedro,

Have you tried to use set analysis for the whole expression?

SUM({< CustomerCode = {'CNA008020'}, PostingDate = {'>=$(=Date(vFIG))<=(=Date(vFFG))'}, AccountNumber = {7090002} >} Amount)

You are using here "PostingDate" field for the date ranges, but it seems there's another field you need to use, or that you need to associate Accounts to the table that is using PostingDate, likely by creating a Master Calendar (if you are not using one already). My guess is that "PostingDate" refers only to Delivery Notes, and that's why it's aggregating only values corresponding to Delivery Notes. Is that correct?

Hope that helps.

[#3]

Thanks for your answer but I doesn't work. Let me show you the script:

Gestion:
qualify *;
unqualify AñoG, MesG, DiaG, PostingDate;
LOAD "Año" As AñoG, Mes as MesG, Dia As DiaG, Date(FechaRegistro,'DD/MM/YYYY') as PostingDate,
CodigoCliente,Cliente, Venta, Coste, Fruta, Variedad, Pais As PaisFruta, Qty, Box, Lote, PteReg, Pte, PaisCliente, Descripcion;
SQL SELECT *
FROM "$(_DBName)"."$(_DBOwner)"."Qlk_Gestion_Temp";
OutOfGestionVentas:
qualify *;
unqualify AñoG, MesG, DiaG, PostingDate;
LOAD "Año" As AñoG, Mes as MesG, Dia As DiaG, ImporteVenta, NCuenta, Date(FechaRegistro,'DD/MM/YYYY') as PostingDate ;
SQL SELECT *
FROM "$(_DBName)"."$(_DBOwner)"."Qlk_OutGestionVentas";

The delivery notes data comes from the Gestion Table and the Amount (ImporteVenta) to sum comes from the OutOfGestionVentas Table. I've unqualified the PostingDate field to make it communal. Is that not correct?

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

I'd concatenate tables Gestion and OutGestion and OutOfGestionVentas, since it seems they have very similar fields, leaving FechaRegistro as the only date field (excluding AñoG, MesG and DiaG), so you have one FechaRegistro alone. Then create a master calendar based on that FechaRegistro.

Hope that helps.

[#5]

Thanks I'll try

[#6]

Hi Miguel, I created the Master Calendar and changing the formula like this, adding the total modificator:

If(Gestion.CodigoCliente='CNA008020',Sum(total {<CalendarDate={'<=$(=Date(vFFG)) >=$(=Date(vFIG))'} ,
 OutGestionVentasl.NCuenta={7090002} >} OutGestionVentas.ImporteCompra),0)

It works!!

Thanks a lot for your time.