Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community,
My dataset looks like:
Customer | RowNumber | BookingDateStart | BookingDateEnd | DaysBetween | Flag | Sales |
123 | 1 | 5-1-2015 | 11-1-2015 | 6 | 1 | 100 |
123 | 2 | 1-2-2015 | 1-3-2015 | 28 | 1 | 150 |
123 | 3 | 10-3-2015 | 1-4-2015 | 22 | 1 | 100 |
123 | 4 | 1-4-2015 | 1-10-2015 | 183 | 0 | 100 |
123 | 5 | 9-5-2015 | 1-10-2015 | 145 | 0 | 150 |
123 | 6 | 1-6-2015 | 1-8-2015 | 33 | 1 | 100 |
123 | 7 | 24-7-2014 | 1-6-2015 | 312 | 0 | 100 |
123 | 8 | 1-1-2015 | 1-9-2015 | 11 | 1 | 150 |
123 | 9 | 1-2-2015 | 1-11-2015 | 34 | 1 | 100 |
In the dashboard I want to make an expression looks like below, but it is not working well.
sum( aggr( {< Flag={1}, vMaxDate={'<BookingDateEnd'} >} Sales * (( BookingDateEnd - vMaxDate) / DaysBetween), RowNumber))
I think the part below is not working well. It looks de aggr() goes wrong.
(( BookingDateEnd - vMaxDate) / DaysBetween)
When vMaxDate = 1-6-2015
The Result I want is:
Customer | RowNumber | BookingDateStart | BookingDateEnd | DaysBetween | Flag | Sales | Result € |
123 | 1 | 5-1-2015 | 1-11-2015 | 300 | 1 | 100 | 51 |
123 | 2 | 1-2-2015 | 1-3-2015 | 28 | 1 | 150 | 0 |
123 | 3 | 10-3-2015 | 1-4-2015 | 22 | 1 | 100 | 0 |
123 | 4 | 1-4-2015 | 1-10-2015 | 183 | 0 | 100 | 0 |
123 | 5 | 9-5-2015 | 1-10-2015 | 145 | 0 | 150 | 0 |
123 | 6 | 1-6-2015 | 1-8-2015 | 61 | 1 | 100 | 100 |
123 | 7 | 24-7-2014 | 1-6-2015 | 312 | 0 | 100 | 0 |
123 | 8 | 1-1-2015 | 1-9-2015 | 243 | 1 | 150 | 56,79 |
123 | 9 | 1-2-2015 | 1-11-2015 | 273 | 1 | 100 | 56,04 |
263,83 |
Customer | Result € |
123 | 263,83 |
Can you help?
Regards,
Arjan
This?
Expression:
=Sum(Aggr(Sum({<Flag = {1}, BookingDateEnd = {'>$(vMaxDate)'}>}Sales) * ((BookingDateEnd - vMaxDate)/(BookingDateEnd - BookingDateStart)), Customer, RowNumber))
May be this:
=Sum({<Flag = {1}, BookingDateEnd = {'>1-6-2015'}>}Sales) * ((BookingDateEnd - '1-6-2015')/(BookingDateEnd - BookingDateStart))
Or this with the variable:
=Sum({<Flag = {1}, BookingDateEnd = {'>$(vMaxDate)'}>}Sales) * ((BookingDateEnd - vMaxDate)/(BookingDateEnd - BookingDateStart))
Thanks for your answer Sunny T, but I'm missing the aggr() in the expression. When I delete the column RowNumber the expression is not working. First the expression must calculate per rownumber the result and then do a Sum()
So you only want to see the result as Customer = 123 and sales = 263.83? No break down is needed?
This?
Expression:
=Sum(Aggr(Sum({<Flag = {1}, BookingDateEnd = {'>$(vMaxDate)'}>}Sales) * ((BookingDateEnd - vMaxDate)/(BookingDateEnd - BookingDateStart)), Customer, RowNumber))
For this example I only want to see the results: Customer and Sales.
Are you sure you need the advanced aggregation? It should work if you include the days calculation into the sum:
=Sum(
{<Flag = {1}, BookingDateEnd = {'>$(vMaxDate)'}>}
Sales * ((BookingDateEnd - vMaxDate)/(BookingDateEnd - BookingDateStart))
)
You are right, I usually have difficult time determining when everything within the Sum would work and when would it not work. I guess its related to aggregation within aggregation kind of concept, right?