Goodday to you all,
I hope someone can give me the hint on which I can move on.
I have been struggling with this for hours now, so a bit of help would be welcome 🙂
My problem is as follows:
I have a straight table containing (more but the two fields it is about are)
Date
Ordernumber
The relation is n:m, so this could exist in the table:
Date Ordernumber
1-1-2021 AAA
1-1-2021 BBB
1-1-2021 CCC
2-1-2021 DDD
2-1-2021 AAA
2-1-2021 BBB
10-1-2021 AAA
10-1-2021 GGG
10-1-2021 BBB
12-1-2021 AAA
12-1-2021 AAA
12-1-2021 GGG
What I want to do is show a table where the date is aggregated to Week (or Month, Quarter, Year)
And next to that I want to show the COUNT(DISTINCT OrderNumber) of the LAST DATE in the selected Date aggregation.
EXAMPLE:
WeekName(Date) COUNT(DISTINCT OrderNumber)
2021/01 3 // being the distinct count of the OrderNumbers of the Last Day (2-1-2021) in this week (1)
2021/02 2 // being the distinct count of the OrderNumbers of the Last Day (12-1-2021) in this week (2)
I have tried using FirstSortedValue in the following way:
=FirstSortedValue( Aggr(COUNT(DISTINCT(OrderNumber),Date)) , -Date)
but that didn't do the trick.
Can anyone tell me what I am doing wrong?
Your help is much appriciated!
Bye
Rob
