Finding min and max based on selection

[krishna20]

Hi Friends,

I have an requirement from the client side that is saying

my input is

ACCOUNT_ID	START_DATE	PREVCLUSTER	CURRCLUSTER
235863	01/01/2016	Neutral	Positive
235863	01/02/2016	Positive	Negative
235863	01/03/2016	Negative	Neutral
235863	01/05/2016	Neutral	Positive
235863	01/06/2016	Positive	Negative
235863	01/07/2016	Negative	Neutral
235863	01/08/2016	Neutral	Negative
316168	01/01/2016	Negative	Positive
316168	01/01/2016	Neutral	Positive
316168	01/02/2016	Positive	Negative
316168	01/03/2016	Negative	Neutral
316168	01/04/2016	Neutral	Negative
316168	01/05/2016	Negative	Neutral
316168	01/06/2016	Neutral	Neutral
316168	01/07/2016	Neutral	Positive
316168	01/08/2016	Positive	Neutral
316168	01/09/2016	Neutral	Positive
397394	01/01/2016	Positive	Negative
397394	01/02/2016	Negative	Neutral
397394	01/03/2016	Neutral	Positive
397394	01/04/2016	Positive	Negative
397394	01/05/2016	Negative	Neutral
397394	01/06/2016	Neutral	Negative
397394	01/08/2016	Negative	Positive

What we need out put here

Scenario1: user select multiple months we need to take min month of PREVCLUSTER and max month of CURRCLUSTER

output:if we selected jan_apr for 235863  min start date of PREVCLUSTER Nuetral and there is no apr month for this account id

this senario we need to take mar as max start date and calculate

Senario2: if you select apr to july for 235863   there is no records for that range of selection

out put:  we need to take may month of PREVCLUSTER and jun month of CURRCLUSTER

do you have any idea on this please suggest me

Thanks

[#2]

Hi Krishna ,

Small clarification ,are you expecting output is date or account -id

[sunny_talwar]

This?

Expressions

1) =FirstSortedValue(Aggr(Concat(DISTINCT PREVCLUSTER, ', '), START_DATE, ACCOUNT_ID), Aggr(START_DATE, ACCOUNT_ID, START_DATE))

2) =FirstSortedValue(Aggr(Concat(DISTINCT CURRCLUSTER, ', '), START_DATE, ACCOUNT_ID), -Aggr(START_DATE, ACCOUNT_ID, START_DATE))