If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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)
The relation is n:m, so this could exist in the table:
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.
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!
Thanks a lot Digvijay,
Looks like it work ... for you 🙂
I have tried to apply your solution, but it failed. Probably because I got stuck in the confusion between fieldnames and functions.
How would it look like if I renamed the fieldnames in
I have adjusted the dates a bit, because the first days of 2021 were in week 2020/53
To aggregate to weeklevel I used "WeekName(Orderdate)"
How does that interfere with your "Week"?