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)
?
Would you share sample
write your code like this:
TEMP:
LOAD
num(min(EmployeeDirectory.DOH)) AS MinDate,
num(max(EmployeeDirectory.DOH)) AS MaxDate
RESIDENT
Employees
;
;
LET vMinDate = peek('MinDate', 0, 'TEMP');;
LET vMaxDate = peek('MaxDate', 0, 'TEMP');;
SET vRangeStart = =vMinDate;
SET vRangeEnd = =vMaxDate;
I don't think that will work as it needs to identify the start and end for each year, not the range.
It's like I need to create a variable for each start end and end for each year and then create the set analysis on that, but not sure where to begin.
Possibly ?
try once and show whatever result will come.
I have attached a sample to the original post.
Any help welcome.
Cheers
Phil
Use FirstSortedValue() like:
Start : FirstSortedValue(Aggr(Sum(Value),Year, Month),Month)
End: FirstSortedValue(Aggr(Sum(Value),Year, Month),-Month)
Set analysis doesn't evaluate row wise, rather once for a chart.
PFA
try this
Sum( total <Dates.FiscalYear> {<[Turnover.Start_Month]={"$(=Date((Min(Turnover.Start_Month)), 'DD/MM/YYYY'))"}>}[Turnover.First_Day_of_Month_Count]))
Hi
Definitely works if there is only one line of data, but there are multiple lines of data for each month as the data is based on headcount by division. e.g. April = Division1 has 1000 & Division2 has 1000 = Total April 2000.
I should have put that on my original message.
Unfortunately your resolution does not work with this.
I have revised the data with your version and attached again so you can see.
So close ......
Thoughts?
Phil