Ask for Expression

[Anonymous]

Hi,

I have a data like this:

ITEM_ID	PART_ID	DATE	AVG_RH		Calculation
P	P1	12-Feb-2018	7288.21		9505.7 - 7288.21
P	P1	14-Feb-2018	7288.21		9505.7 - 7288.21
P	P1	06-Jun-2018	8726.46		9505.7 - 7288.21
P	P1	13-Aug-2018	9505.7		9505.7 - 7288.21
P	P2	05-Jan-2018	6732.4		7288.21-6732.4
P	P2	22-Jan-2018	7060.95		7288.21-6732.4
P	P2	29-Jan-2018	7162.55		7288.21-6732.4
P	P2	05-Feb-2018	7267.1		7288.21-6732.4
P	P2	12-Feb-2018	7288.21		7288.21-6732.4

I want to get AVG_RH value in max(DATE) and Min(DATE) for each ITEM_ID and PART_ID.

For example ITEM_ID = P and PART_ID = P1

I want to get AVG RH in MAX DATE (13-Aug-2018) and MIN DATE(12-FEB-2018) so the result must be in Calculation column above.

Can somebody help me.

Thanks

[Anonymous]

Where do you want to see the result? text object?

[PrashantSangle]

Is avg rh coming  from back end or you are calculating it at front end???

Regards,

[Anil_Babu_Samineni · Accepted solution]

Try this way? Not tested - But this may work.

If(DATE=Max(TOTAL <PART_ID> DATE), Sum(TOTAL <DATE,PART_ID> AVG_RH)) & '-' & FirstSortedValue(TOTAL <PART_ID> AVG_RH, Aggr(Sum(AVG_RH), PART_ID))

Or

If(DATE=Max(TOTAL <PART_ID> DATE), Sum(TOTAL <DATE,PART_ID> AVG_RH), Sum({<DATE={"$(=Max(DATE))"}>} AVG_RH)) & '-' &

FirstSortedValue(TOTAL <PART_ID> AVG_RH, Aggr(Sum(AVG_RH), PART_ID))

Or

FirstSortedValue(TOTAL <PART_ID> AVG_RH, -Aggr(Sum(AVG_RH), PART_ID))-FirstSortedValue(TOTAL <PART_ID> AVG_RH, Aggr(Sum(AVG_RH), PART_ID))

[Anonymous]

Hi,

Thanks for your solution, it solved my problem perfectly !

[Anil_Babu_Samineni]

Glad it worked, Which one worked for you?