topic Problem with using "getcurrentfield" function with "Aggr" in QlikView

Problem with using "getcurrentfield" function with "Aggr"

Thu, 14 Apr 2011 19:31:23 GMT

Hi Everyone,

I have the requirement to show the top 5 selection results based on cycle group selection. So i tried to use the getcurrentfield function in aggr funtion.But its not working as expected. I used below expression. If i replace getcurrentfield function with field name its working fine. Could some one help me how to fix this. Please see the attached QVW file to be clear.

Below expression is with getcurrentfield function:

=if(aggr(rank(sum(Qty)),getcurrentfield(New))<5,getcurrentfield(New))

Below expression with field name instead of getcurrentfield function.this is working fine.

=if(aggr(rank(sum(Qty)),ID)<5,ID,'others')

Thanks for your help in advance

Problem with using "getcurrentfield" function with "Aggr"

chris_johnson — Thu, 14 Apr 2011 21:59:27 GMT

Hi,

I've had a quick look and put together a top 5 list based on your dimension cycle group. I don't think you need to use the aggr function as the totals will be aggregated over the relevant dimension to start with.

If I have understood your question correctly I think most of what you need can be done using the properties in the straight table.

Have a look and see what you think

Regards,

Chris

Problem with using "getcurrentfield" function with "Aggr"

johnw — Thu, 14 Apr 2011 22:07:48 GMT

Just use New instead of getcurrentfield(New). You can use a cyclic field just like any other field. You only need to fiddle with getcurrentfield() when you want a TEXT description of which field is current.

Problem with using "getcurrentfield" function with "Aggr"

Thu, 14 Apr 2011 22:31:26 GMT

Thank you very much for the reply.

Chris, The top 5 is not fixed number. Based on the user input number will change. So i used Aggr function.

John, I try to use the just cycle group name "New" without getcurrentfield function. its working fine but its not giving the option to select the other cycle dimentions. Its showing chart for only one fixed dimention. Please see the attached QVW.

Problem with using "getcurrentfield" function with "Aggr"

johnw — Thu, 14 Apr 2011 22:49:12 GMT

Chris's approach is still good. Just replace the 5 with $(v_top). It does require locking the sort order, though, or you'll get the bottom five instead, for instance.

This is ugly, but as an alternative that doesn't require locking the sort order, you could create a different cyclic group. Add expressions instead of fields, one for each field. So in your example, put these expressions in a cyclic group:

=if(aggr(rank(sum(Qty)),ID)<=$(v_top),ID,'other')
=if(aggr(rank(sum(Qty)),Month)<=$(v_top),Month,'other')

Label them as desired, then use that group instead. Seems to work.

Problem with using "getcurrentfield" function with "Aggr"

Thu, 14 Apr 2011 22:59:35 GMT

With straight table approach i cant show the others total. Above solution worked fine. I appriciate your help. Thankyou verymuch.

Problem with using "getcurrentfield" function with "Aggr"

johnw — Thu, 14 Apr 2011 23:23:25 GMT

Do you mean there wasn't a number on the "others" line? I noticed that when I first tried it. It looks like the answer is to set the total mode to the sum of rows instead of expression total.

Problem with using "getcurrentfield" function with "Aggr"

Fri, 15 Apr 2011 00:00:51 GMT

Yes john, I was not able to see any number on others line. As you suggested, once i changed the totals mode to sum of rows,its working fine. Thanks for your help.

Problem with using "getcurrentfield" function with "Aggr"

nathanfurby — Fri, 03 Jun 2011 12:08:08 GMT

For future reference - Use dollar sign to get the string value of the currentfield:

=if(aggr(rank(sum(Qty)),$(=getcurrentfield(New)))<5,$(=getcurrentfield(New)),'Others')