Set Analysis with Aggregation Function to get Max Datetime Sales based on Busines ID

[brijeshvma]

Hi All,

I am Trying to get set analysis with Aggregation function , but its not happening  kindly help me to resolve this.

I Created Sample Excel Input data as well Out what i needed.

INPUT DATA :

BusinessID	TransDate	TransDateTime	Sales Value
2146	04-10-2016	04-10-16 2:57:47 PM	26.8
2146	05-10-2016	05-10-16 5:03:58 PM	35.8
2146	06-10-2016	06-10-16 12:35:11 PM	1585.8
2146	07-10-2016	07-10-16 6:50:11 PM	1760.3
2146	08-10-2016	08-10-16 5:59:00 PM	2.3
2146	10-10-2016	10-10-16 4:48:59 PM	403.8
2146	12-10-2016	12-10-16 2:03:04 PM	403.8
2146	14-10-2016	14-10-16 1:08:43 PM	76.3
2146	21-10-2016	21-10-16 8:57:59 PM	66.3
2146	26-10-2016	26-10-16 8:30:13 PM	358.8
2147	05-10-2016	05-10-16 10:27:49 PM	7357.6
2147	06-10-2016	06-10-16 1:06:33 PM	2984.6
2147	07-10-2016	07-10-16 12:29:06 PM	2931.25
2147	10-10-2016	10-10-16 11:18:15 AM	2984.6
2147	13-10-2016	13-10-16 12:45:34 PM	1946.6
2147	15-10-2016	15-10-16 5:55:00 PM	16513.6
2147	18-10-2016	18-10-16 3:51:46 PM	9262.6
2147	19-10-2016	19-10-16 5:04:12 PM	68371.6
2147	20-10-2016	20-10-16 11:25:07 AM	63351.6
2147	23-10-2016	23-10-16 11:46:45 PM	58137.6
2147	24-10-2016	24-10-16 9:25:30 PM	57143.04
2147	30-10-2016	30-10-16 6:24:23 PM	52123.04
2179	04-10-2016	04-10-16 6:15:31 PM	32.18
2179	14-10-2016	14-10-16 5:38:32 PM	16532.18
2179	15-10-2016	15-10-16 4:10:14 PM	7589.68
2179	17-10-2016	17-10-16 4:59:31 PM	31.68
2179	18-10-2016	18-10-16 7:00:24 PM	6382.28
2179	19-10-2016	19-10-16 5:41:49 PM	651.28
2179	21-10-2016	21-10-16 5:10:09 PM	1605.78
2179	22-10-2016	22-10-16 4:56:20 PM	1217.28
2179	23-10-2016	23-10-16 6:54:04 PM	589.28
2179	24-10-2016	24-10-16 6:13:04 PM	10907.68
2179	25-10-2016	25-10-16 5:13:57 PM	780.28
2179	30-10-2016	30-10-16 12:40:02 PM	21.18

FIRST OUTPUT: if Cust not Selected any Date it need to Show max Datetime Sales value based on Business ID.

BusinessID	TransDate	TransDateTime	Sales Value
2146	26-10-2016	26-10-16 8:30:13 PM	358.8
2147	24-10-2016	24-10-16 9:25:30 PM	57143.04
2179	30-10-2016	30-10-16 12:40:02 PM	21.18

SECOND OUTPUT : IF Cust selected Say 25 Oct 2016. So Max Datetime Sales Value Till 25 Oct 2016.

BusinessID	TransDate	TransDateTime	Sales Value
2146	21-10-2016	21-10-16 8:57:59 PM	66.3
2147	24-10-2016	24-10-16 9:25:30 PM	57143.04
2179	25-10-2016	25-10-16 5:13:57 PM	780.28

[brijeshvma]

Kindly Help me to Resolve this.. I tried lot but its heppening .. Please check and Suggest the output..

[sunny_talwar]

Please find attached

Dimension

BusinessID

Expressions

=FirstSortedValue({<TransDate = {"$(='<=' & Date(Max(TransDate)))"}, TransDateTime>}TransDate, -TransDateTime)

=TimeStamp(Max({<TransDate = {"$(='<=' & Date(Max(TransDate)))"}, TransDateTime>}TransDateTime))

=FirstSortedValue({<TransDate = {"$(='<=' & Date(Max(TransDate)))"}, TransDateTime>}[Sales Value], -TransDateTime)

[brijeshvma]

Hi Sunny ,

It looks Correct , But can You help me Give sum as well in Text .. Because it giving currently max value in KPI

Say if i dont select anything , 52503.02 and if i select 25 OCt 2016 than 57989.62 ..

Kindly help me to resolve this..

[sunny_talwar · Accepted solution]

May be this

=Sum({<TransDate = {"$(='<=' & Date(Max(TransDate)))"}, TransDateTime>}Aggr(FirstSortedValue({<TransDate = {"$(='<=' & Date(Max(TransDate)))"}, TransDateTime>}[Sales Value], -TransDateTime), BusinessID))

[brijeshvma]

Thanks you so much Sunny.. It giving Accurate Output now. Thanks for Time and Effort as usual..

[swearengen]

Great stuff. Very useful thanks.