How to calculate latest invoice and order date?

[chriscools]

Hello,

i'm having a date issue, i'm making a pivot table with my client as a dimension and a few calculations:

     -total invoiced amount

     -total orders

     -...

But i also want to show the latest day the client had an invoice, and the latest day he made an order.

i made an expression:

     max (invoicedate)

     max(orderdate)

but that gives me the latest date when there was any fact linked to that client, for example a picking...

How should i do it to show me the latest date for invoice and orders?

thanx,

chris

[swuehl]

How does your script look like? How went the fact dates into your calendar?

Have you considered a link table between your fact table and your calendar?

edit: see also Henric's blog about calendar and

Tutorial - Using Common Date Dimensions and Shared Calendars

[chriscools]

Hey,

there i'm a littlebit stuck i'm afraid...

our software provider wrote the script and unfortunately that's something i'm not skilled in...

i was hoping that i was using a wrong expression or something like that....

[fkeuroglian]

Hi, would be easy if you put and example to get the solution

Thank you

Fernando

[chriscools]

Hey,

that's a good idea! 🙂

i posted an example with 1 pivot table containing the information i want to be able to show.

and then a few tabels showing the invoices, orders, with their dates for this client.

thanx!

grtz,

chris

[preminqlik]

hi there try this

=max({1}OrderDate)
=max({1}Date)
=max({1}InvoiceDate)

[vardhancse]

Hi,

If we take a dimension order date, It will have multiple dates.

Out of which if we want latest date we can use MAX function.

Eg:

Create one variable: vOrderDate = Max(OrderDate)

vInvoiceDate = Max(InvoiceDate)

SO that we can use this variable all the time in expression so that it will display latest date.

[chriscools]

Hello,

this still gives me the same date?

grtz,

chris

[chriscools]

Hello,

when i do this, if you look at the file included this also still gives me the same date as before?

grtz,

chris

[preminqlik]

hi there once try this

=max({<OrderDate={'=OrderDate=InvoiceDate'}>}OrderDate)

=max({<OrderDate={'=OrderDate=InvoiceDate'}>}InvoiceDate)