Show attribute of Contract for the Max Report Date, using two different sources

[lylererger]

Hi.

Task: to present in your chart the status of the contract maximum date of the report.

The user selects a date in the chart reflects the latest status of the contract, i.e. contract status for the maximum date.

Everything works fine if I using single data source.

But if I concatenate xls file to the fact table, the algorithm stops working.

I checked, field formats, both sources are unified.

Please explain to me why my algorithm is not working.

Maybe I'm doing something wrong, or maybe i should write ticket to the qliktech .

Expression, to calculate max report date for the contract looks like:

=Max({<[Report Date]={'<=$(vEndReport)'}>}

  Aggr(

  Max({<[Report Date]={'<=$(vEndReport)'}>}[Report Date])

  ,[Contract ID]))

Sources and source code in the attachment.

Hope for yours help.

Thnx in advance.

[Gysbert_Wassenaar]

Try =FirstSortedValue(Status,-[Report Date])

[lylererger]

Thnx Gysbert. Yes it works, but i need to show single status in straigh-table the last one on report date selected in calendar

[Gysbert_Wassenaar]

Create a straight table with no dimensions and that one expression I posted above.

[petter · Accepted solution]

This always results in a single result value. Maybe you should consider having a sequence for your Status like using Dual to get them in a sorting sequence - that way you can get Max Date and Max Status for that particular Date.

[lylererger]

Colleagues thanks a lot.

Peter Concat works fine. An interesting approach.