Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
For our sales team I want to display a chart that shows months as dimension and the counts of first as well as last orders of customers as Y-values. So first order means "new customer" and last order shows when the last order of a customer was placed.
Now I figured out how to count the first orders:
=Sum(If(Aggr( Min([Posting Date]), Name) = [Posting Date], 1))
Result: 39 New Customers spread across serveral months
But when I try to do the same for last orders by exchanging Min for Max, the result ge
Latest Orders:
=Sum(If(Aggr( Max([Posting Date]), Name) = [Posting Date], 1))
Result: Only 12 Last Orders.
Logically thinking, every Cusomer should have a first and a last order. So both values should have the same sum in total.
I just don't get the mistake... 🙂
Thanks a lot in advance!!
Timm
result table (diagram):
YearMonth | last order | first order |
12 | 39 | |
201208 | 0 | 2 |
201209 | 1 | 3 |
201212 | 1 | 1 |
201302 | 0 | 1 |
201304 | 0 | 2 |
201305 | 2 | 3 |
201307 | 2 | 3 |
201308 | 0 | 1 |
201309 | 0 | 1 |
201310 | 3 | 5 |
201312 | 0 | 1 |
201401 | 0 | 2 |
201403 | 0 | 2 |
201405 | 0 | 3 |
201407 | 0 | 2 |
201409 | 1 | 4 |
201410 | 0 | 1 |
201412 | 1 | 1 |
201503 | 1 | 1 |
I don't think it will be the same result and your expression compared an aggregation directly with a field without aggregation which will be always return NULL if there isn't a unique value. I believe your expression should be more look like this:
=Sum(Aggr(sum({< [Posting Date] = {"$(=Max([Posting Date]))"}>}1), Name, YearMonth))
- Marcus
if I understood right, what you want to do is to get the min and max posting date for each customer, right ?
=Sum(If(Aggr( Max(total <Name>[Posting Date]), Name) = [Posting Date], 1))
=Sum(If(Aggr( Min(total <Name>[Posting Date]), Name) = [Posting Date], 1))