Max value from a specific table of a synthetic key

[matthieu_k]

Hi,

I have a date field which is a synthetic key between my sales and my targets.

Targets are known until end of year but sales are only on year to date basis.

I want to know in a table per product the last time I sold the product so max(date_key)

But I get 31/12 from the objective table.

Any idea how to specify the max from the sales table ?

Thanks

Matthieu

[marcus_sommer · Accepted solution]

You need to include anything unique from the related table, for example a KPI and then the expression may look like:

max({< KPI = {'Sales'}>} date_key)

Beside this I suggest to remove the synthetic key by a different kind of data-model and not associating the fact-tables else by concatenating them. It's much simpler and avoids a lot of trouble - not only in regard to associate the various fact- and dim-tables else also by having missing key-values on both sides which isn't seldom.

By concatenating the tables you should add an extra source-field which you could then use instead of the above mentioned KPI.

Another approach might be also to create a flag-field within the calendar, like:

if(Date <= today(), 'past', 'future') as flag  

[matthieu_k]

Thanks Marcus for your quick reply.

I will setup flag then

For the key, I will refer your point to the expert in my team when he is back from holidays 🙂

Enjoy your day,

Matthieu