Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Working to show only single scheduled date for the date that each person had the maximum hours during a selected time frame in pivot table with 3 dimensions.
Current table view
Would like it to only display 7/23 (Highlighted) date for both since that was max actual.
Change your dimension that contains the date field to this:
=aggr(FirstSortedValue(DateField, -[Actual Clocked Hours]), Dimension1, Dimension2)
If I'm understanding correctly, the following should do the trick:
=max(TOTAL <Dimension1, Dimension2, Dimension3> [Actual Clocked Hours])
No, sorry I was probably not clear enough.
Would like to only see 1 line under each persons name with the date for the highest hours within that week.
Change your dimension that contains the date field to this:
=aggr(FirstSortedValue(DateField, -[Actual Clocked Hours]), Dimension1, Dimension2)
My scheduled hours are still showing the weekly total. Any thoughts or would probably need to load sample?
I would suggest making a flag using a left join like this:
Data:
LOAD * INLINE [
Dimension1, Dimension2, DateField, Actual Clocked Hours
EAGLEFORD, Name 1, 7/22/2017, 15.58
EAGLEFORD, Name 1, 7/23/2017, 16.10
EAGLEFORD, Name 1, 7/24/2017, 9.25
EAGLEFORD, Name 2, 7/22/2017, 15.73
EAGLEFORD, Name 2, 7/23/2017, 16.08
EAGLEFORD, Name 2, 7/24/2017, 9.67
EAGLEFORD, Name 2, 7/25/2017, 15.47
EAGLEFORD, Name 2, 7/26/2017, 15.12
EAGLEFORD, Name 2, 7/27/2017, 14.97
EAGLEFORD, Name 2, 7/28/2017, 14.93
];
LEFT JOIN (Data)
LOAD Dimension1,
Dimension2,
FirstSortedValue(DateField, -[Actual Clocked Hours]) AS DateField,
1 AS Max_Flag
RESIDENT Data
GROUP BY Dimension1, Dimension2;
Then on your chart, you can have three dimensions:
And your expression can be:
I have also attached an example file with the above code.