Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

Can someone please advise?

Thank you!

Emily

8 Replies
sushil353
Master II
Master II

What is the expression to calculate the variable?

Not applicable
Author

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()

))

))

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

What are the dimensions in the chart?

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

This still gives me 0 for everything.