Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I Need to calculate : Sum of the purchases of 1st tickets of the customers, sorted out by 1st week of purchase
I write the dimension with no problem at all : =week(aggr(min([Date ticket]),[Code conso]))
for the indicator I make the test :
=sum({<[Date ticket]={"=aggr(min([Date ticket]),[Code conso])"}>}[CA])
But for certain customers the result is false .
the Data is
Consumer ID | Ticket Date | ligne ticket ID | Ticket ID | PRODUCT | Amount | Quantity |
357789 | 06/12/2014 | 281151170 | 87488143 | 93081_01_19/22 | 11 | 1 |
357789 | 06/12/2014 | 281151171 | 87488143 | 18021_01_12M | 45 | 1 |
357789 | 06/12/2014 | 281151172 | 87488143 | 30121_01_12M | 69 | 1 |
357789 | 06/12/2014 | 281151173 | 87488143 | 90141_01_46 | 25 | 1 |
357789 | 30/05/2015 | 298252889 | 92124169 | 25002_45_2A | 25,2 | 1 |
357789 | 30/05/2015 | 298252890 | 92124169 | 10022_77_2A | 21 | 1 |
I want the sum of amount of the 1rst ticket (06/12/2014) of the consumer 357789 : 150
Any other idea?
I put in attached files an example of application and the expected result(profit)
thanks
And now tested in your qvw, for all customers, can you try?:
Sum(Aggr(If([Ticket ID]=Min(TOTAL <[Consumer ID]> [Ticket ID]), Sum(Amount)), [Consumer ID], [Ticket ID]))
Hi Magali, can you try?;
For one customer: FirstSortedValue(Sum(Amount), [Ticket ID])
For all customers: Sum(Aggr(FirstSortedValue(Sum(Amount), [Ticket ID]), [Consumer ID]))
And now tested in your qvw, for all customers, can you try?:
Sum(Aggr(If([Ticket ID]=Min(TOTAL <[Consumer ID]> [Ticket ID]), Sum(Amount)), [Consumer ID], [Ticket ID]))
thanks a lot for your help Ruben.
My answers :
For one customer: FirstSortedValue(Sum(Amount), [Ticket ID]) => error in expression
For all customers: Sum(Aggr(FirstSortedValue(Sum(Amount), [Ticket ID]), [Consumer ID])) => 0
and the third is OK !
Sum(Aggr(If([Ticket ID]=Min(TOTAL <[Consumer ID]> [Ticket ID]), Sum(Amount)), [Consumer ID], [Ticket ID]))
I understood your expression. I do not know well the Aggregation functions but thanks to you I progressed a lot today. Thank you very much