Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
TauseefKhan
Contributor II
Contributor II

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

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          

 

Labels (4)
4 Replies
vinieme12
Champion III
Champion III

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;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
TauseefKhan
Contributor II
Contributor II
Author

Thanks for your reply,

We need this in formula expression, not load script.

 

vinieme12
Champion III
Champion III

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

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

@TauseefKhan kindly close the thread if your query is resolved

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.