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

[Anil_Babu_Samineni]

Would you share sample

[sarvesh]

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;

[Anonymous]

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

[Anonymous]

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 ?

[sarvesh]

try once and show whatever result will come.

[Anonymous]

I have attached a sample to the original post.

Any help welcome.

Cheers

Phil

[tresesco]

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

[Kushal_Chawda]

try this

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

[Anonymous]

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