12 Replies Latest reply: Mar 29, 2017 8:08 AM by Phil Speight

# Dates in set analysis

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

• ###### Re: Dates in set analysis

Would you share sample

• ###### Re: Dates in set analysis

TEMP:

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;

• ###### Re: Dates in set analysis

I don't think that will work as it needs to identify the start and end for each year, not the range.

• ###### Re: Dates in set analysis

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 ?

• ###### Re: Dates in set analysis

try once and show whatever result will come.

• ###### Re: Dates in set analysis

I have attached a sample to the original post.

Any help welcome.

Cheers

Phil

• ###### Re: Dates in set analysis

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

• ###### Re: Dates in set analysis

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

• ###### Re: Dates in set analysis

What if you use DISTINCT like:

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

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

?

• ###### Re: Dates in set analysis

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

• ###### Re: Dates in set analysis

try this

Sum( total <Dates.FiscalYear> {<[Turnover.Start_Month]={"\$(=Date((Min(Turnover.Start_Month)), 'DD/MM/YYYY'))"}>}[Turnover.First_Day_of_Month_Count]))

• ###### Re: Dates in set analysis

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