Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
May be this
=Sum({<TransDate = {"$(='<=' & Date(Max(TransDate)))"}, TransDateTime>}Aggr(FirstSortedValue({<TransDate = {"$(='<=' & Date(Max(TransDate)))"}, TransDateTime>}[Sales Value], -TransDateTime), BusinessID))
Kindly Help me to Resolve this.. I tried lot but its heppening .. Please check and Suggest the output..
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)
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..
May be this
=Sum({<TransDate = {"$(='<=' & Date(Max(TransDate)))"}, TransDateTime>}Aggr(FirstSortedValue({<TransDate = {"$(='<=' & Date(Max(TransDate)))"}, TransDateTime>}[Sales Value], -TransDateTime), BusinessID))
Thanks you so much Sunny.. It giving Accurate Output now. Thanks for Time and Effort as usual..
Great stuff. Very useful thanks.