Dates in set analysis

[Anonymous]

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

[tresesco · Accepted solution]

What if you use DISTINCT like:

Start : FirstSortedValue( Distinct Aggr(Sum(Value),Year, Month),Month)

End: FirstSortedValue( Distinct Aggr(Sum(Value),Year, Month),-Month)

?

[Anonymous]

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

[Anonymous]

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