Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
swebster
Contributor II
Contributor II

How to count the maximum value for each distinct ID?

I am trying to get a bar chart to show the number of times each maximum value occurs, per distinct ID. The maximum value can be an integer between 1 and 6. An example of data is below:

Episode IDMost recent form ID
1013
1012
1011
2502
2501
552
551
6384
6383
6382
6381

I am wanting to show a count of the maximum number in the 'most recent form ID' for each distinct episode ID. So, for example, the maximum value of the form ID for episode 101 is 3, so this counts as 1 episode ID. 2 is the maximum number for IDs 55 and 250, so this should count 2 distinct episode IDs. However, my current formula is currently counting every distinct episode ID that has a 2 (even if it is not the max value), so in this example it is returning a count of 4 (not 2).

My current formula is this:

Max(Aggr(Count({<[Opened in past 12 months?] = {'Yes'}>} distinct [Caf Episode ID]), [Most recent Form ID]))


And this produces the following graph:

2dd8645025a443f188b690840c3cb2b7.jpg

I know this is wrong, because when I drill down into each column, the single episode ID that falls under '6' also appears in every other 'most recent form ID' group - but I only want it to appear in '6', because this is the maximum 'most recent form ID' for this distinct Episode ID...

Can anyone help? Thank you in advance!

1 Solution

Accepted Solutions
sunny_talwar

May be try with a calculated dimension

Aggr(Max(ID), [Episode ID])

Expression

Count(DISTINCT [Episode ID])

View solution in original post

4 Replies
sunny_talwar

May be try with a calculated dimension

Aggr(Max(ID), [Episode ID])

Expression

Count(DISTINCT [Episode ID])

swebster
Contributor II
Contributor II
Author

Thank you Sunny, that worked! Most appreciated.

PolBe
Contributor III
Contributor III

hi Sunny,

How would you fix this then?

First, in Straight table  to have the max ID per Episode ID:

101 - 3

250 - 2

55 - 2

638 - 4

and then in Bar chart to have the average in general for all Episode ID's?

Avg =  2.75

((3+2+2+4)/4)

Thank you in advance

sagarpawar
Contributor III
Contributor III

Hi,

Can you please help me with proper expression.

 

Thanks,

Sagar

SGP