Date in Set Analysis

m_perreault — Mon, 25 Jun 2018 20:38:37 GMT

Hi All,

I have the two tables in the image below. ID Number 1A564DDS has three TransactionDates shown in the top table and has MonthEnd Price's shown in the bottom table. I want to write an expression that shows the Price at the time of the transaction. I have tried using the expression Only({<[Data As Of Date] = {"$(=Date(MonthEnd(TransactionDate),'M/DD/YYYY'))"}>}[Price - Hist]) and its giving me null values. When I hard code a date such as

Only({<[Data As Of Date] = {'11/30/2017'}>}[Price - Hist])

It gives me what I expect. Does anyone know how to accomplish what I am trying to do?

Thanks,
Mark

Re: Date in Set Analysis

marcus_sommer — Tue, 26 Jun 2018 11:40:20 GMT

Monthend() returned no date else a timestamp which means you need an additionally rounding like:

Only({<[Data As Of Date] = {"$(=Date(floor(MonthEnd(TransactionDate)),'M/DD/YYYY'))"}>}[Price - Hist])

Further you need to ensure that there is only a single TransactionDate is available and otherwise this call will return NULL because there is no aggregation like max(TransactionDate).

But if I look again on your screenshot it looked that you want to apply this expression on a record-level which couldn't work in this way because you used a $-sign expansion which mean that this part is globally calculated before the other parts of the expression and used there for each row.

You could try it without the $-sign expansion but it don't worked in each case but maybe ...

Only({<[Data As Of Date] =

{"=[Data As Of Date]=Date(floor(MonthEnd(TransactionDate)),'M/DD/YYYY')"}>}

[Price - Hist])

- Marcus

topic Date in Set Analysis in App Development

Date in Set Analysis

Re: Date in Set Analysis