Nested calculated dimension or cominig two expressions in it

[guyvermeiren]

Hello,

I think this is realy difficult.

I want to limit my table to a Top 10 what isn't difficult but I want a top 10 for my cutomers who are advancing the most and had a turnover last year that was above a certain limit.

These are the two expressions that I try to combine in my calculated dimension but how do I say in my formula that I first want to limit the list to customers with a certain turnover and then only shoow the top 10 (by rank and "Suppress when value is Null" .

Expression 1: limit turnover

=If(aggr(sum({$<[COPA.Fiscal Year]= {$(=max([Calendar.Year])-1)},COPA.Period= {'*'} >} [COPA.Niko Act Gross Sales incl Projects Rebates NL/FR]),

[GeneralPayerMasterData.Customer Name&Code])>15000,[GeneralPayerMasterData.Customer Name&Code],null())

Expression 2: Rank on evolution

=If(aggr(rank(( Sum( {$<[COPA.Fiscal Year]= {$(=max([Calendar.Year]))} , [COPA.Period]= {">$(=min([Calendar.Month])-1)<$(=max([Calendar.Month])+1)"} >} [COPA.Niko Act Gross Sales incl Projects Rebates NL/FR] )/ Sum( {$<[COPA.Fiscal Year]= {$(=max([Calendar.Year]-1))} , [COPA.Period]= {">$(=min([Calendar.Month])-1)<$(=max([Calendar.Month])+1)"} >} [COPA.Niko Act Gross Sales incl Projects Rebates NL/FR] ) )-1),[GeneralPayerMasterData.Customer Name&Code])<=10,

[GeneralPayerMasterData.Customer Name&Code],Null())

Thanks.

Kind regards,

Guy

[tresesco]

Hi Guy,

Set analysis does not work in calculated dimension. let the customer be your dimension, then you should calculate the RANK on your turnover limit condition in the expression of the chart and then sort it and finally limit it to 10 in the presentation tab.

Regards, tresesco

[guyvermeiren]

Hi Tresesco,

We already worked with a kind of set analysis (formula) in a calculated dimension and it worked.

About limit to 10 via presentation tab: The problem is that I'm working with a pivot table and I don't think that I have the possibility there.

Thanks anyway.

Kind regards,

Guy