Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Rob29
Contributor II
Contributor II

How to retrieve the COUNT on the last row of an aggregation

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

Labels (2)
1 Solution

Accepted Solutions
Digvijay_Singh
Master III
Master III

May be something like this using firstsortedvalue

Digvijay_Singh_0-1620689044930.png

 

View solution in original post

4 Replies
Digvijay_Singh
Master III
Master III

May be something like this using firstsortedvalue

Digvijay_Singh_0-1620689044930.png

 

View solution in original post

Rob29
Contributor II
Contributor II
Author

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

Orderdate  Ordernumber

5-1-2021     AAA
5-1-2021      BBB
5-1-2021      CCC
6-1-2021      DDD
6-1-2021      AAA
6-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

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"?

Rob29
Contributor II
Contributor II
Author

I have worked some more on it and it works!!! Great! Thank you very much Digvijay!

 

Digvijay_Singh
Master III
Master III

You may close this thread by marking helpful/Like/Correct Solutions to indicate that you got what you needed.