Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I am trying to create a calculated dimension that only returns the yellow rows below, the last two cases done each day based on Out Room Time. Any ideas? I feel like I need to use AGGR but im not sure in what sense. Thank you!
T:
LOAD
Date,
[Out Room Time],
Case
From < SourceTable > ;
NoConcatenate
F:
LOAD Date,
FirstSortedValue([Out Room Time], -[Out Room Time]) as [Out Room Time],
FirstSortedValue(Case, -[Out Room Time]) as Case
Resident T
Group By Date;
LOAD Date,
FirstSortedValue([Out Room Time], [Out Room Time], 2) as [Out Room Time],
FirstSortedValue(Case, [Out Room Time], 2) as Case
Resident T
Group By Date;
DROP Table T;
T:
LOAD
Date,
[Out Room Time],
Case
From < SourceTable > ;
NoConcatenate
F:
LOAD Date,
FirstSortedValue([Out Room Time], -[Out Room Time]) as [Out Room Time],
FirstSortedValue(Case, -[Out Room Time]) as Case
Resident T
Group By Date;
LOAD Date,
FirstSortedValue([Out Room Time], [Out Room Time], 2) as [Out Room Time],
FirstSortedValue(Case, [Out Room Time], 2) as Case
Resident T
Group By Date;
DROP Table T;
So I would have to do this in the load script and not actually build a calculated dimension in the chart?
In this situation, I think the most effective approach would be at the backend.