Aggr Function

[#1]

Hi All,

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

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

Thanks in Advance,

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

[#3]

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)) 

Thanks in Advance,

[sunny_talwar]

What about this:

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

[Chanty4u]

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

[#6]

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.

[sunny_talwar]

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

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

[#8]

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...

[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))

[#10]

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.