Set analysis aggr() expression

[Anonymous]

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

[sunny_talwar]

May be this:

=Sum({<Flag = {1}, BookingDateEnd = {'>1-6-2015'}>}Sales) * ((BookingDateEnd - '1-6-2015')/(BookingDateEnd - BookingDateStart))

[sunny_talwar]

Or this with the variable:

=Sum({<Flag = {1}, BookingDateEnd = {'>$(vMaxDate)'}>}Sales) * ((BookingDateEnd - vMaxDate)/(BookingDateEnd - BookingDateStart))

[Anonymous]

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()

[sunny_talwar]

So you only want to see the result as Customer = 123 and sales = 263.83? No break down is needed?

[sunny_talwar · Accepted solution]

This?

Expression:

=Sum(Aggr(Sum({<Flag = {1}, BookingDateEnd = {'>$(vMaxDate)'}>}Sales) * ((BookingDateEnd - vMaxDate)/(BookingDateEnd - BookingDateStart)), Customer, RowNumber))

[Anonymous]

For this example I only want to see the results: Customer and Sales.

[swuehl]

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

)

[sunny_talwar]

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?