topic Re: Aggr Function using Cyclic Groups in QlikView

Aggr Function using Cyclic Groups

Thu, 19 Nov 2009 19:52:07 GMT

Hi Folks

I have a challenge for you all......

I have an expression in a straight table that calculates values according to current and prior fx rates and summarises by an investment name, the table has the investment name as a dimension and the following as an expression:

=Num(
 Sum(Aggr(
 (
 ( Sum ( _rFMV )
 *
 ( Round(Max(FX_toUSD_RepDate),0.000001) - Round(Max(FX_toUSD_CompDate),0.000001) ) )
 /
 Round(Max(FX_toUSD_RepDate),0.000001)
 )
 , RG_Investment, _pCCY
 ))

 / vDivisor ,vNumberFormat
 )

Now the above works perfectly and gives me exactly the results I need.

The problem:

The straight table where I want to put this is using a cyclic group rather than the investment name, so the aggregation should go over the current active field of the cyclic group. I have created the following expression:

=Num(
 Sum(Aggr(
 (
 ( Sum ( _rFMV )
 *
 ( Round(Max(FX_toUSD_RepDate),0.000001) - Round(Max(FX_toUSD_CompDate),0.000001) ) )
 /
 Round(Max(FX_toUSD_RepDate),0.000001)
 )
 , GetCurrentField("XXX_CycleGroup_Valuations"), _pCCY
 ))

 / vDivisor ,vNumberFormat
 )

You will notice that the only difference between them is the use of GetCurrentField("XXX_CycleGroup_Valuations") in place of RG_Investment.

This expression produces results of zero across the board, and now I'm stuck, any help would be very much appreciated.

Aggr Function using Cyclic Groups

Thu, 19 Nov 2009 23:02:06 GMT

Hi Nigel,

I've tried make an example close to yours but you use a lot fields which I don't understan, however, I made my own example using the same filosofy like yours.

I think the sentence GetCurrentField("XXX_CycleGroup_Valuations") doesn't work because the function GetCurrentField returns a text (Field name) and not a Field as itself. So I found a possible solution, using something like if(GetCurrentField(MyGroup) = 'Key', Key, key2) for replacing the sentence. Check out my example and I hope you will understan it better.

The drawback is remembering add all fields included in the group now and in a future.

I hope this helps you.

Best regards.

Aggr Function using Cyclic Groups

Thu, 19 Nov 2009 23:15:38 GMT

Hi Miguel

Thanks for the response, I had come to the same conclusion myself and have implemented your suggestion.

As you say, the problem is remembering in the future when I add fields to the group (but maybe I'll have left the company by then)!

Cheers,

Aggr Function using Cyclic Groups

Tue, 19 Jan 2010 10:22:51 GMT

I had a similar problem and the solution, at least to my problem, was very simple. I just used the cyclic group as a field. In your case just try to use

[XXX_CycleGroup_Valuations] instead of GetCurrentField("XXX_CycleGroup_Valuations").

Aggr Function using Cyclic Groups

Tue, 19 Jan 2010 13:35:17 GMT

Yup, agree with Bigakis, the answer in this case was very simple, just use the name of the cyclic group.

Cheers,

Re: Aggr Function using Cyclic Groups

Thu, 08 Aug 2013 10:33:26 GMT

Yep it's working. thank u