showing "historical" data of same table in a virtual column

[Asheetbogo]

hi everyone!
i want to show "historical" data of my table "history" in a virtual column (VHistory):
Workaround: Table 1: projects + Table 2: history

Goal:
a) for each project in table "history" i only want to see the latest entry (e.g. Tool XS: 2023 > finished)
b) a history of all entrys of the past "compressed" in virtual collumn "VHistory" (a) 2019: started – a) 2020: supended ...)

this is how it should look like (i put this together with copy and paste in my photo-editor .... )
>>> how can i acchieve this?

Thank you very much in advance for your reply"

[dbaum · Accepted solution]

I don't fully understand what you have and what you want. Here's a possible approach in case it's helpful.

In your History table, add a summary column that aggregates the information you want from each row for your summary. For example:

[Year] & ": " & [Status]

In your VHistory column join the values of that new summary column using whatever delimiter you prefer.

Configure your History view to show data from a slice that includes only the row(s) you want.

[Asheetbogo]

hello @dbaum

thank you so much for your prompt reply - i will test it out an be back if i need further help!

[Asheetbogo]

hello @dbaum

i tested it out - it works perfect:

A) SliceEpression:

[Year] = MAX(SELECT(history[Year], [Project] = [_THISROW].[Project]))
>>> This showing only one rown for every project (>>> the newest (Year) status of each project  >>> MAX([Year]  ....)

B) Virtual columns "Vhistory"

SELECT(
history[Status],
AND(
[Project] = [_THISROW].[Project],
[Year] <= [_THISROW].[Year]
)
)

>>> this is summing up the historical status of the project in Virtual Column "VHistory"

HistoryTable (with serveral rows for each project ...):

result: History Table (compressed: only one row for each project ...)

thank you so much!