topic Re: Aggr Function in QlikView

Aggr Function

Thu, 25 Feb 2016 17:26:17 GMT

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,

Re: Aggr Function

sunny_talwar — Thu, 25 Feb 2016 17:30:25 GMT

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?

Re: Aggr Function

Thu, 25 Feb 2016 17:36:14 GMT

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,

Re: Aggr Function

sunny_talwar — Thu, 25 Feb 2016 17:41:28 GMT

What about this:

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

Re: Aggr Function

Chanty4u — Thu, 25 Feb 2016 17:42:30 GMT

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

Re: Aggr Function

Thu, 25 Feb 2016 17:49:24 GMT

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.

Re: Aggr Function

sunny_talwar — Thu, 25 Feb 2016 17:53:07 GMT

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

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

Re: Aggr Function

Thu, 25 Feb 2016 18:04:25 GMT

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

Re: Aggr Function

sunny_talwar — Thu, 25 Feb 2016 18:07:46 GMT

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

Re: Aggr Function

Thu, 25 Feb 2016 18:34:02 GMT

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.

Re: Aggr Function

sunny_talwar — Thu, 25 Feb 2016 18:36:43 GMT

Would it be possible to share a sample?

Re: Aggr Function

Thu, 25 Feb 2016 18:42:41 GMT

Hi Sunny,

Please find attached the sample data set.

Thanks,

Re: Aggr Function

sunny_talwar — Thu, 25 Feb 2016 18:54:42 GMT

I think there are 190 distinct values

Re: Aggr Function

Thu, 25 Feb 2016 20:19:09 GMT

Thanks for all your help Sunny.

Re: Aggr Function

Thu, 25 Feb 2016 22:14:12 GMT

Hi Sunny,

This formula you had suggested to me. This formula works well only with the four quarters is there anyway I can make it the Rolling Quarters concept. If the end user clicks on Feb it would give the end user Nov thru Jan, if he/she clicks on Mar the formula would give the end user Dec thru Feb.

Please help!!!!!!!!!!!

=money(Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount) -

Sum({[Book1]<MC.QuarterNum={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, 4, Max({$}MC.QuarterNum)-1))"}, [MC.DimYear] ={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, Max({$}[MC.DimYear]) - 1, Max({$}[MC.DimYear])))"}>} MC.grossamount)) * 4 /

Sum({[Book2]<MC.QuarterNum={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, 4, Max({$}MC.QuarterNum)-1))"}, [MC.DimYear] ={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, Max({$}[MC.DimYear]) - 1, Max({$}[MC.DimYear])))"}>} MC.grossamount)

Re: Aggr Function

sunny_talwar — Fri, 26 Feb 2016 00:01:34 GMT

Look here, I learned something new from Oleg: rolling 3 months not working properly with set analysis...?

Re: Aggr Function

Fri, 26 Feb 2016 13:27:51 GMT

Sorry Sunny, I don't comprehend the post. Is rolling months possible in this case? I need to make two text boxes, in one I would show the four quarters and in another one text box would show the rolling quarters (e.g.Nov to Jan, Dec to Feb, Feb to April).

=money(Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount) -

Sum({[Book3]<MC.QuarterNum={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, 4, Max({$}MC.QuarterNum)-1))"}, [MC.DimYear] ={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, Max({$}[MC.DimYear]) - 1, Max({$}[MC.DimYear])))"}>} MC.grossamount)

Please help!!!!!!!!!!!

Re: Aggr Function

sunny_talwar — Fri, 26 Feb 2016 13:37:22 GMT

‌I am traveling right now... But if nobody help you resolve your issue, I am more than happy to take a quick look at your issue