Date problem

[#1]

Hi all.

I have a problem with the range of dates, I hope someone can help me.

I have two tables, notes and invoices, the key field is a MAKEDATE (year, month, day). Two weeks ago not invoiced sales are not updated, return values ??are changed only when invoiced sales and not invoiced as null. In 2011 it works !!

Anyone know what could happen? attachment qvw

[hic]

I am not sure I understand your question completely, but I think that a part of the problem lies in the fact that you have entries in your Details table for which there are no corresponding entry in the Facturas table.

Then you create your master calendar based on the dates in the Facturas table. As a consequence you have dollars in the Details tablethat are associated with dates that do not exist in the other two tables, e.g. for the date '16/01/2012'.

But if this is the entire problem, I do not know.

[#3]

Thanks for the reply Henrik.

I understand what you mean, is logical, but this document has been working for 6 months without problems, began to fail in 2012. What is the explanation for this?

Thanks and regards

[Miguel_Angel_Baeyens]

Hi Gabriel,

Try wraping all MakeDate() functions by Date() functions and see how that works. For example:

DATE(MAKEDATE(FVCOEJ,FVGEME,FVGEDI)) AS FECHA

Hope that helps.

Miguel

[hic]

The problem is probably in the SQL database. You use MAKEDATE(B2GEAN,B2GEME,B2GEDI) in the Details table and MAKEDATE(FVCOEJ,FVGEME,FVGEDI) in the Invoice table. That should work.

So I suspect that the invoice table (S65FCEEB.APLREMGEFI.FV) isn’t updated correctly. Check if the fields FVCOEJ,FVGEME,FVGEDI in it have correct values or if there are records missing in it (records that have corresponding values in the Details table).

[fernandotoledo · Accepted solution]

I think you might be missing some dates. Try concatenating the dates from both tables before finding max and min values!

And try to use the PEEK() function in this case. FieldValue() is used in chart expressions.

CALENDARIO_TEMP:
LOAD distinct
          FECHA
RESIDENT FACTURAS;
CONCATENATE LOAD distinct
          FECHA
RESIDENT ALBARAN_DETALLE_VENTA;
CALENDARIO_TEMP_II:
LOAD
          Max(FECHA) AS DateMax,
          Min(FECHA) AS DateMin
RESIDENT CALENDARIO_TEMP;
LET vMaxDate = FieldValue('DateMax', 1);
LET vMinDate = FieldValue('DateMin', 1) -1; 
DROP TABLES CALENDARIO_TEMP,CALENDARIO_TEMP_II;

[#7]

Hello everyone and thanks for your answers.

Miguel, the proposed solution returns the same values.

Henric, I think the same as you but developers say the management application that everything is correct, the details corresponding fields are 0 except FVCOEJ is right.

Fernando, your solution seems to work.

I have to say that today, miraculously, without making any changes, the document seems to work correctly.

greetings