Firstsorted Value by Date and Time

[rustyfishbones]

Hi All,

I am trying to use an expression in a straight table to show the first transaction for each day.

I have the following Table

And I want the following result

So I need the first R transactions for each Day based on the time.

I hope you can help.

Date	Product	StartTime	Transaction Type	Amount
08/03/2017	BB4BL264R	07:02:38	R	1
08/03/2017	MAAX94S	07:03:17	R	1
08/03/2017	RB783351	07:03:27	D	1
09/03/2017	BB4BL238R	06:41:41	D	1
09/03/2017	RB786048	06:59:57	R	1
09/03/2017	RB786055	07:00:14	R	1
10/03/2017	JV60096	07:02:56	R	1
10/03/2017	JV60096	07:02:59	R	1
10/03/2017	MP002676	07:03:17	R	1
Date	Product	StartTime	Transaction Type	Amount
08/03/2017	BB4BL264R	07:02:38	R	1
09/03/2017	RB786048	06:59:57	R	1
10/03/2017	JV60096	07:02:56	R	1

[Kushal_Chawda]

Have you tried my solution?

[rustyfishbones]

No, I want to do it as an expression for now, I don't really want to change the script right now.

I have some success with the following expression

AGGR(FirstSortedValue([Transaction Type], Time#(StartTime, 'hh:mm:ss')),Date)

So I would like to SUM the Amount where the transaction type = R

[Kushal_Chawda]

try this

FirstSortedValue(DISTINCT {<[Transaction Type]={'R'}>} Amount , aggr(Time#(only({<[Transaction Type]={'R'}>} StartTime), 'hh:mm:ss'),Date))       

[rustyfishbones]

I have tried that and it returns nothing, I will keep trying, I know it will be a simple solution in the end.

[tresesco]

Hi,

Did you check my last reply?

[rustyfishbones]

Yes, I ca see it's working in your file, nut not in mine??

[rustyfishbones]

Hi,

It's working now with the following

AGGR(FirstSortedValue({<[Transaction Type] = {'R'}>} [Transaction Type], Time#(StartTime, 'hh:mm:ss')),Date)

[sunny_talwar · Accepted solution]

Another option

Dimension

Date

Transaction Type

Expressions

Only({<[Transaction Type] = {'R'}>}Aggr(If(StartTime = Min(TOTAL <Date> {<[Transaction Type] = {'R'}>} StartTime), Only({<[Transaction Type] = {'R'}>}Product)), Product, Date, StartTime))

Time(Min({<[Transaction Type] = {'R'}>}Time#(StartTime,'hh:mm:ss')))

Sum({<[Transaction Type] = {'R'}>}Aggr(If(StartTime = Min(TOTAL <Date> {<[Transaction Type] = {'R'}>} StartTime), Sum({<[Transaction Type] = {'R'}>}Amount)), Product, Date, StartTime))

Used tresesco‌'s sample file to create a new chart (thanks)

Best,

Sunny

[rustyfishbones]

I jumped the gun, still not correct.

[rustyfishbones]

Thanks Sunny, that worked perfectly!