topic Re: Set Analysis with Aggregation Function to get Max Datetime Sales based on Busines ID in QlikView

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

brijeshvma — Wed, 25 Nov 2020 16:16:04 GMT

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

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

brijeshvma — Thu, 16 Mar 2017 09:28:56 GMT

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

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

sunny_talwar — Thu, 16 Mar 2017 09:41:23 GMT

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)

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

brijeshvma — Thu, 16 Mar 2017 09:56:13 GMT

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

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

sunny_talwar — Thu, 16 Mar 2017 10:00:34 GMT

May be this

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

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

brijeshvma — Thu, 16 Mar 2017 10:20:23 GMT

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

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

swearengen — Thu, 06 Oct 2022 11:16:00 GMT

Great stuff. Very useful thanks.