How to use set analysis with search related to an expression in a Table

[sfatoux72]

Hi,

I want to find a "Bonus Multiplier" based on objective.

A Table with my Objective (annual objective) and related Bonus Multiplier by Year and Store

T_Objective:

LOAD Year & '_' & Store as OBJ_Key,

  Objective,

  [Bonus Multiplier]

INLINE [

    Year, Store, Objective, Bonus Multiplier

    2015, Store A, 0, 0

    2015, Store A, 150, 0.8

    2015, Store A, 250, 1

    2015, Store A, 350, 1.2

    2015, Store B, 0, 0

    2015, Store B, 150, 0.8

    2015, Store B, 250, 1

    2015, Store B, 350, 1.2

];

A Table with my Sales by Year, Month and Store

T_Sales:

LOAD Year & '_' & Store as OBJ_Key,

  *

INLINE [

    Year, Store, Month, Sales

    2015, Store A, 1, 12

    2015, Store A, 2, 15

    2015, Store A, 3, 12

    2015, Store B, 1, 30

    2015, Store B, 2, 29

    2015, Store B, 3, 27

];

I calculate a yearly projection of Sales  :   

=sum(Sales) / count(Distinct Month) *12

I use it a set analysis to determine my Bonus Multiplier :

= Max({<Objective = {"<$(=sum(Sales) / count(distinct Month) * 12)"}>}[Bonus Multiplier])

That works fine when I select a Store.

But when I use it in a table by Store or in an AGGR, that don't work.

I suppose, that is because the expression in the set analysis are evaluated outside the table (or AGGR) and I have the projection of all Store instead of Store by Store.

How could I manage that differently?

[swuehl · Accepted solution]

Your assumption is correct, the set analysis is evaluated once per chart, not per store.

You can try something like

=Max( Aggr( if( Objective < sum({<Year={$(=max(Year))}>}Sales) / count({<Year={$(=max(Year))}>} distinct Month) * 12, [Bonus Multiplier]),Store, Objective))

You can also try to assign the bonus in the script, if you don't need to regard selections.

[sfatoux72]

Hi Swuehl,

I thought to do it in the script:

• group by to calculate yearly projection by year
• interval match to assign bonus

But I knew that was possible in an expression with an AGGR, and you give me the solution.

Many thanks