Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggr Function

Hi All,

Can someone tell me what I'm doing wrong with this function.

=if(Aggr(rank(count(DISTINCT(change_label='expansion')),d1subcust_clientname)))

Capture.PNG

Thanks in Advance,

1 Solution

Accepted Solutions
sunny_talwar

I think there are 190 distinct values

Capture.PNG

View solution in original post

17 Replies
sunny_talwar

Is this the complete expression? you have a if without true or false condition. Also your functions are all looking out of place. What are you trying to do?

Not applicable
Author

Hi Sunny,

I'm trying to get the distinct function to work. I want to use it as a chart expression. When I use the below function it gets me duplicate lines.

=count(if(change_label='expansion',d1subcust_clientname)) 

Capture.PNG

Thanks in Advance,

sunny_talwar

What about this:

=Count(DISTINCT if(change_label='expansion',d1subcust_clientname))

Chanty4u
MVP
MVP

=count(Distinct if(change_label='expansion',d1subcust_clientname))

Not applicable
Author

Hi Sunny,

The formula works great with Text Box but when I apply it at straight table it only shows the total count. When I try to remove the count under the expression it shows an error.

Capture.PNG

sunny_talwar

Does this in the straight table give you the same result?

=Count(DISTINCT {<change_label = {'expansion'}>} d1subcust_clientname))

Not applicable
Author

I need a list box expression which would provide me with no duplicates. If I apply the =Count(DISTINCT {<change_label = {'expansion'}>} d1subcust_clientname)) as a list box expression it gives me 143 which includes duplicates as you can see the frequency.


The number is much smaller than 143 but the frequency shows 8,4,4,etc...


Capture.PNG

sunny_talwar

Don't have the ability to test, so once again guessing, but try this:

Sum({<change_label = {'expansion'}>}Aggr(Count(Distinct {<change_label = {'expansion'}>}d1subcust_clientname), d1subcust_clientname))

Not applicable
Author

The reason why it's showing a frequency of more than one is because of association model Qlikview has built in. Client Name has contract lines (like a father might have kids). As you can see once I click on the first client name with 8 frequency the 8 Contract Lines get filtered out. In reality there are about 25 - 35 client names but due to their relationship with contract lines it adds up. Thanks for your help Sunny. Is there any formula you can suggest which will exclude this relationship with contract line and provide me with the right number of client names under expansion.

Capture.PNGCapture1.PNG