last invoice date

[#1]

Dear all,

I'm trying to calculate the last invoice date based on a version date (snapshot date).

In order to accomplish this I have loaded both the snapshot dates as well as the detailed inventory movements as seperate tables.

The next step is to calculate the last invoice date at each snapshot date and this is where i got stuck.

I created a cartesian product of the version dates and all movements.

In a next step I'm trying to calculate the last invoice date given the version date as follows:

Rotation:

load VersionDate,

     Material,

     Warehouse,

     if(Date#(InvoiceDate) <= Date#(VersionDate), max(IssueDate),'Unknown') as LastInvoiceDate

Resident VersionDate

group by VersionDate, SSP, Material;

The result is en 'Error in expression'. Can anyone what is wrong in this statement?

Thank you very much in advance,

Kr,

K

ps: The difficulty here is that the last invoice date for a certain material at a certain warehouse could be several months earlier than the month of the snapshot but then that still remains the last invoice date.

[sushil353 · Accepted solution]

Try this:

Rotation:

load

max(IssueDate) as LastInvoiceDate

VersionDate

     Material,

     SSP

  Resident VersionDate

where InvoiceDate <= VersionDate

group by VersionDate, SSP, Material;

HTH

Sushil

[tresesco]

Try including SSP in the load like:

Rotation:

load VersionDate,

     SSP,

     Material,

     Warehouse,

     if(Date#(InvoiceDate) <= Date#(VersionDate), max(IssueDate),'Unknown') as LastInvoiceDate

Resident VersionDate

group by VersionDate, SSP, Material;