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

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

TauseefKhan — Thu, 19 May 2022 06:43:55 GMT

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

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

vinieme12 — Thu, 19 May 2022 07:39:37 GMT

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;

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

TauseefKhan — Thu, 19 May 2022 11:12:35 GMT

Thanks for your reply,

We need this in formula expression, not load script.

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

vinieme12 — Thu, 19 May 2022 11:21:27 GMT

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))

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

vinieme12 — Fri, 20 May 2022 06:29:57 GMT

@TauseefKhan kindly close the thread if your query is resolved