How To get exclude Min and Max date Transaction for each Script Id by expression.

[TauseefKhan]

Below is the Original data we need an expression to be created which will exclude min and max date transaction and give us all the transaction for a particular script id.

Original Data					Out put required after applying the expression
Script Id	Date	Amount			Script Id	Date	Amount
Item A	01-01-2022	100			Item A	01-02-2022	75
Item A	01-01-2022	50			Item A	01-03-2022	150
Item A	01-02-2022	75			Item A	01-04-2022	100
Item A	01-03-2022	150			Item A	01-05-2022	50
Item A	01-04-2022	100			Item A	01-06-2022	25
Item A	01-05-2022	50			Item B	01-03-2022	10
Item A	01-06-2022	25			Item B	01-03-2022	30
Item A	01-07-2022	50			Item B	01-03-2022	60
Item A	01-07-2022	75			Item B	01-04-2022	30
Item B	01-02-2022	40			Item B	01-05-2022	20
Item B	01-03-2022	10			Item B	01-06-2022	10
Item B	01-03-2022	30
Item B	01-03-2022	60
Item B	01-04-2022	30
Item B	01-05-2022	20
Item B	01-06-2022	10
Item B	01-08-2022	20
Item B	01-08-2022	30

[vinieme12]

as below

Main:
Load ScriptId,date#(Date,'MM/DD/YYYY') as Date,Amount inline [
ScriptId,Date,Amount
Item A,01/01/2022,100
Item A,01/01/2022,50
Item A,01/02/2022,75
Item A,01/03/2022,150
Item A,01/04/2022,100
Item A,01/05/2022,50
Item A,01/06/2022,25
Item A,01/07/2022,50
Item A,01/07/2022,75
Item B,01/02/2022,40
Item B,01/03/2022,10
Item B,01/03/2022,30
Item B,01/03/2022,60
Item B,01/04/2022,30
Item B,01/05/2022,20
Item B,01/06/2022,10
Item B,01/08/2022,20
Item B,01/08/2022,30
];

inner Join(Main)
Load
ScriptId as ScriptId
,Date(mindt+Iterno()) as Date
While mindt+Iterno() < maxdt
;
Load ScriptId,min(Date) as mindt,max(Date) as maxdt
Resident Main
Group by ScriptId;

[TauseefKhan]

Thanks for your reply,

We need this in formula expression, not load script.

[vinieme12]

Either use a calculated dimension for Date field

Dimensions

ScriptID

=Aggr(if(Date>min(total <ScriptId> Date) and Date<max(total <ScriptId> Date),Date),ScriptId,Date)  <<for Date calculated dimension

Measure

sum(Amount)

OR restrict in measure as below

Dimensions

ScriptID, Date

Measure

=sum(aggr(if(Date>min(total <ScriptId> Date) and Date<max(total <ScriptId> Date),Amount),ScriptId,Date))

[vinieme12]

@TauseefKhan kindly close the thread if your query is resolved