Formule

[Former Member]

Hi Experts,

I have this table:-

ID	Desc		Effective Dt
12	AAA		2012-12-23
12	AAA		2014-02-06
12	BBB		2011-12-22
12	BBB		2012-12-23
12	BBB		2013-12-29

I have a requirement to get the [Desc] for min Effective Date

[Desc] have 2 fixed value i.e AAA and BBB but each [Desc] has different Effective Date.

I want to select only Distinct [Desc] with min Effective date for each [ID]

Desired Output:-

ID	Desc		Effective Dt
12	AAA		2012-12-23
12			2014-02-06
12	BBB		2011-12-22
12			2012-12-23
12			2013-12-29

Formule i Used

=[Desc] Where([Effective Dt]=Min([Effective Dt]) In([Customer Id]))

but no success

[Former Member · Accepted solution]

Hello Sagar,

Please create a measure as [MinEffDate]=Min([Effective Dt]) ForAll([Effective Dt])

Then Create another dimension as [Modified Desc]=[Desc] Where([Effective Dt]=[MinEffDate]) In([Desc];[ID])

Then just pull ID, Modified Desc and Effective Date in your report.

Make sure that you check the Option Avoid Duplicate Row Aggregation on the Block Properties.

This will give you expected result.

Regards

Niraj

[Former Member · Accepted solution]

Thank Niraj. U too Good

[Former Member]

Hi Sagar,

Maybe I am oversimplifying here... but can you acheive your desired output by just applying a simple break on 'DESC'? and set it to only display the first value?

Sort your 'Effective Date' from minumum to maximum and apply a break on 'DESC'. I have attached a screenshot that matches your desired output table

WTJ

[Former Member]

Hi,

Use this formula for effective date

[Effective date]=Min([Effective Dt])

Regards,

Raju Kushwaha

[Former Member]

No success!

[Former Member]

hi,

if field [Effective Dt] is string  then convert into date

Regards,

Raju Kushwaha