Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

last invoice date

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.

1 Solution

Accepted Solutions
sushil353
Not applicable

Re: last invoice date

Try this:

Rotation:

load

max(IssueDate) as LastInvoiceDate

VersionDate

     Material,

     SSP

  Resident VersionDate

where InvoiceDate <= VersionDate

group by VersionDate, SSP, Material;

HTH

Sushil

2 Replies
sushil353
Not applicable

Re: last invoice date

Try this:

Rotation:

load

max(IssueDate) as LastInvoiceDate

VersionDate

     Material,

     SSP

  Resident VersionDate

where InvoiceDate <= VersionDate

group by VersionDate, SSP, Material;

HTH

Sushil

tresesco
Not applicable

Re: last invoice date

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;