Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a set analysis that looks at the min & max date (as below)
(Sum({<
[Turnover.Start_Month]={"$(=Date((Min(Turnover.Start_Month)), 'DD/MM/YYYY'))"}
>} [Turnover.First_Day_of_Month_Count]))
This works fine without the added dimension of fiscal year, However I now need to bring in the Fiscal year as a dimension in my pivot table, but my table shows as below for the start and end headcount.
Dates.FiscalYear | Start Headcount | End Headcount | Overall Leavers | ||
15/16 | 6608 | 0 | 844 | ||
16/17 | 0 | 7067 | 707 |
The set analysis uses the whole range to work out the min & max, but I need to show a start and end headcount for each year. Any ideas?
Thanks
Phil
What if you use DISTINCT like:
Start : FirstSortedValue( Distinct Aggr(Sum(Value),Year, Month),Month)
End: FirstSortedValue( Distinct Aggr(Sum(Value),Year, Month),-Month)
?
Surely there must be a way to overcome this. Pulling my hair out here.
Anyone any ideas?
attached sample again in case you need it.
Cheers
Phil
Fantastic !!!!!!!!!
It works a treat.
Unfortunately as it is correct, it has identified a slightly different problem in one of the other expressions (unrelated), but I am going to post this separately to see if I can get resolution.
Tresesco, thank you so much !!
Phil