Skip to main content
Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
brijeshvma
Partner - Creator
Partner - Creator

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

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 :

BusinessIDTransDateTransDateTimeSales Value
214604-10-201604-10-16 2:57:47 PM26.8
214605-10-201605-10-16 5:03:58 PM35.8
214606-10-201606-10-16 12:35:11 PM1585.8
214607-10-201607-10-16 6:50:11 PM1760.3
214608-10-201608-10-16 5:59:00 PM2.3
214610-10-201610-10-16 4:48:59 PM403.8
214612-10-201612-10-16 2:03:04 PM403.8
214614-10-201614-10-16 1:08:43 PM76.3
214621-10-201621-10-16 8:57:59 PM66.3
214626-10-201626-10-16 8:30:13 PM358.8
214705-10-201605-10-16 10:27:49 PM7357.6
214706-10-201606-10-16 1:06:33 PM2984.6
214707-10-201607-10-16 12:29:06 PM2931.25
214710-10-201610-10-16 11:18:15 AM2984.6
214713-10-201613-10-16 12:45:34 PM1946.6
214715-10-201615-10-16 5:55:00 PM16513.6
214718-10-201618-10-16 3:51:46 PM9262.6
214719-10-201619-10-16 5:04:12 PM68371.6
214720-10-201620-10-16 11:25:07 AM63351.6
214723-10-201623-10-16 11:46:45 PM58137.6
214724-10-201624-10-16 9:25:30 PM57143.04
214730-10-201630-10-16 6:24:23 PM52123.04
217904-10-201604-10-16 6:15:31 PM32.18
217914-10-201614-10-16 5:38:32 PM16532.18
217915-10-201615-10-16 4:10:14 PM7589.68
217917-10-201617-10-16 4:59:31 PM31.68
217918-10-201618-10-16 7:00:24 PM6382.28
217919-10-201619-10-16 5:41:49 PM651.28
217921-10-201621-10-16 5:10:09 PM1605.78
217922-10-201622-10-16 4:56:20 PM1217.28
217923-10-201623-10-16 6:54:04 PM589.28
217924-10-201624-10-16 6:13:04 PM10907.68
217925-10-201625-10-16 5:13:57 PM780.28
217930-10-201630-10-16 12:40:02 PM21.18

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

    

BusinessIDTransDateTransDateTimeSales Value
214626-10-201626-10-16 8:30:13 PM358.8
214724-10-201624-10-16 9:25:30 PM57143.04
217930-10-201630-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.

    

BusinessIDTransDateTransDateTimeSales Value
214621-10-201621-10-16 8:57:59 PM66.3
214724-10-201624-10-16 9:25:30 PM57143.04
217925-10-201625-10-16 5:13:57 PM780.28
1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

6 Replies
brijeshvma
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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

May be this

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

brijeshvma
Partner - Creator
Partner - Creator
Author

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

swearengen
Contributor III
Contributor III

Great stuff. Very useful thanks.