topic last invoice date in QlikView

last invoice date

Wed, 09 Apr 2014 09:16:57 GMT

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,

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.

Re: last invoice date

sushil353 — Wed, 09 Apr 2014 09:20:47 GMT

Try this:

Rotation:

load

max(IssueDate) as LastInvoiceDate

VersionDate

Material,

SSP

Resident VersionDate

where InvoiceDate <= VersionDate

group by VersionDate, SSP, Material;

HTH

Sushil

Re: last invoice date

tresB — Wed, 09 Apr 2014 09:23:34 GMT

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;