Aggr function nested with dimension calculated

[cocuzzan]

Hi

I need to make simple calculations to see but I have difficulty.

The problem is that I have to:

0) given a list of customers month by month, with relative amounts, and for each customer the corresponding TYPE in that month for that customer

1) calculate a dimension calculated for each customer for the TYPE field. Precisely if a customer has been LOY for 5 months and then has become LIF in the following months, at the end of the year it is always LOY for me - done and it's simple!

2) For each Dimension calculated, add the amount - DONE

3) Calculate the calculated size with more ammount - PROBLEM !!!

As you can see from the attached sample app. the problem is in having within an AGGR, another AGGR that has a DIMENSION CALCULATED as DIMENSION. given the app can you solve point 3 ????

SET SumAmount=  sum(AMOUNT);

SET TypeYear = (if(count({< TYPE={'VUL'}>} DISTINCT ID_CUSTOMER) >0,'VUL',
if(count({< TYPE={'WIN'}>} DISTINCT ID_CUSTOMER) >0,'WIN',
if(count({< TYPE={'NEW'}>} DISTINCT ID_CUSTOMER) >0,'NEW',
if(count({<TYPE={'LOY'}>} DISTINCT ID_CUSTOMER) >0,'LOY',
if(count({<TYPE={'LIF'}>} DISTINCT ID_CUSTOMER) >0,'LIF',
'?')))))
);

SET TypeCalculated   = AGGR ( $(TypeYear)   ,ID_CUstomer);

SET DimensioneCalculated =  aggr (  $(ammount) , $(TypeCalculated)) ;    ////******* How Can I do ?

[vinieme12]

Try below

if (aggr( rank( $(ammount)) , $(TypeCalculated) )=1, $(TypeCalculated) )

[cocuzzan]

already tried. Correct syntax but does not work. Thanks for trying

[vinieme12]

Instead of creating calculated dimension use set analysis

TypeYear can just be = TYPE

SumAmount  =  sum({<TYPE={'VUL','VIN','NEW','LOY','LIF'}>}AMOUNT)

THEN

if (aggr( rank( sum({<TYPE={'VUL','VIN','NEW','LOY','LIF'}>}AMOUNT) , TYPE )=1, TYPE)

[cocuzzan]

I'm sorry but the exercise calculation is not what you are proposing. I need to calculate the Type and aggregate it by ID_CUSTOMER, and then retrieve the TYPE_calcolated with the highest revenues .. please download the attached app. thanks

[vinieme12]

Unfortunately I cannot open your app 

But , one thing you must be aware of is aggr can only be done on physical fields that exists in your data model 

[cocuzzan]

yes I am aware, so I wonder how would you do the calculation? How would you take the description of a dimension calculated with the max of values? if you see attached image, the required calculation should be clear. thanks

[cocuzzan · Accepted solution]

I have resolved!

//aggr(if(rank(VALUE_RANKING,4,2)=1,_DIMENSION_CALCULATED),DATA_FIELD_DIM_CALCULATED)

aggr(if(rank(sum( AMOUNT) ,4,2)=1,

//dimension calculated - start
(if(count({< TYPE={'VUL'}>} DISTINCT ID_CUSTOMER) >0,'VUL',
if(count({< TYPE={'WIN'}>} DISTINCT ID_CUSTOMER) >0,'WIN',
if(count({< TYPE={'NEW'}>} DISTINCT ID_CUSTOMER) >0,'NEW',
if(count({<TYPE={'LOY'}>} DISTINCT ID_CUSTOMER) >0,'LOY',
if(count({<TYPE={'LIF'}>} DISTINCT ID_CUSTOMER) >0,'LIF',
'?')))))
))
//dimension calculated - finish

,ID_CUSTOMER)