8 Replies Latest reply: Jun 5, 2013 2:21 PM by Emily Eddings

# Sum and Aggr Set Analysis Question

Hi everyone,

I am relatively new to set analysis and have a quick question.  I have a variable that I am trying to display disregarding the current selection.

I have the following formula:

sum({1}aggr(\$(Total_Win_Index), [Search ISO Week End Date])) where \$(Total_Win_Index) is my variable.

This displays the correct result for the week selected, and nothing for all other weeks.  I would expect it to display everything and disregard the current selection.

Thank you!

Emily

• ###### Re: Sum and Aggr Set Analysis Question

What is the expression to calculate the variable?

• ###### Re: Sum and Aggr Set Analysis Question

It's a long one, and there are other variables referenced within it...

((if(GetFieldSelections([Rate Type])='Base Rate',

(1+ ((sum(if(\$(base_inventory_flag)=1 and \$(comp1_inventory_flag)<>1,1,0))

+

sum(if(\$(base_inventory_flag)=1 and \$(comp2_inventory_flag)<>1,1,0))

+

\$(base_comp1_win_baserate)

+

\$(base_comp2_win_baserate)

)

-

(sum(if(\$(comp1_inventory_flag)=1 and \$(base_inventory_flag)<>1,1,0))

+

sum(if(\$(comp2_inventory_flag)=1 and \$(base_inventory_flag)<>1,1,0))

+

\$(base_comp1_loss_baserate)

+

\$(base_comp2_loss_baserate)

))

/

(\$(base_comp1_shops) + \$(base_comp2_shops))

)

,

if(GetFieldSelections([Rate Type])='Total Price',

(1+ ((sum(if(\$(base_inventory_flag)=1 and \$(comp1_inventory_flag)<>1,1,0))

+

sum(if(\$(base_inventory_flag)=1 and \$(comp2_inventory_flag)<>1,1,0))

+

\$(base_comp1_win_totalprice)

+

\$(base_comp2_win_totalprice)

)

-

(sum(if(\$(comp1_inventory_flag)=1 and \$(base_inventory_flag)<>1,1,0))

+

sum(if(\$(comp2_inventory_flag)=1 and \$(base_inventory_flag)<>1,1,0))

+

\$(base_comp1_loss_totalprice)

+

\$(base_comp2_loss_totalprice)

))

/

(\$(base_comp1_shops) + \$(base_comp2_shops))

)

, Null()

))

))

• ###### Re: Sum and Aggr Set Analysis Question

You need to move {1} inside aggr. Sometimes it can be done with only(), e.g.

sum( {1} aggr(  only( {1} \$(Total_Win_Index)), [Search ISO Week End Date]))

Yet, it may depend on dimensions and Total_Win_Index variable.

• ###### Re: Sum and Aggr Set Analysis Question

Thank you, but this gives me 0 for all weeks.  Any other suggestions?

• ###### Re: Sum and Aggr Set Analysis Question

What are the dimensions in the chart?

• ###### Re: Sum and Aggr Set Analysis Question

The only dimension is Search ISO Week End Date.  I wouldn't even need a sum and aggregate function I don't think except that I want to do set analysis to disregard the current selection.

• ###### Re: Sum and Aggr Set Analysis Question

Yes, one more suggestion -- it seems that aggr supports set analysis, so use {1} directly inside of aggr, insteady of only().

• ###### Re: Sum and Aggr Set Analysis Question

This still gives me 0 for everything.