Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
BI_Dev
Creator II
Creator II

Max of count for each month

 Month  Weekday # Distinct COUNT Max OUTPUT
Dec 2024 Tue 982 982
Dec 2024 Tue 968  
Nov 2024 Wed 931 931
Nov 2024 Tue 888  
Oct 2024 Tue 915 915
Oct 2024 Tue 894  


I need the max output in the chart by Month and I have the below expression 

max(aggr(count(distinct [ID]),[Month])) and it is not working


Labels (4)
1 Solution

Accepted Solutions
PrashantSangle

using @rubenmarin1 solution

max(total<Month> aggr(count(distinct [ID]),[Date])).

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

7 Replies
rubenmarin1

Hi, you can use: Max(TOTAL <Month> [# Distinct COUNT])

Or, if you want to show the values only on the max value row: If(Max(TOTAL <Month> [# Distinct COUNT])=[# Distinct COUNT],Max(TOTAL <Month> [# Distinct COUNT]))

 

PrashantSangle

If you want to do same in script then use Window ()

Load *, Window(Count(ID),Month) as max_month_count

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Chanty4u
MVP
MVP

Try this 

Max(Aggr(Count(Distinct [ID]), [Month], [Weekday]))

 

BI_Dev
Creator II
Creator II
Author

@Chanty4u @rubenmarin1 @PrashantSangle That did not work - expressions gave the below result - 

BI_Dev_1-1740963258365.png

Max number of sales is 982.

Now I want to show Max # of sales for each month - her for Dec 982 and Nov 931.

Expression for Mac overall is 

max(total aggr(count(distinct [ID]),[Date]))

 

poklegoguy
Creator
Creator

Try this:

IF(AGGR(count(distinct [ID]),Month,Weekday) = MAX(TOTAL <Month> AGGR(count(distinct [ID]),Month,Weekday)), count(distinct [ID]))

PrashantSangle

using @rubenmarin1 solution

max(total<Month> aggr(count(distinct [ID]),[Date])).

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
BI_Dev
Creator II
Creator II
Author

@PrashantSangle - Thank you. That worked.