topic Re: Sum and Aggr Set Analysis Question in QlikView

Sum and Aggr Set Analysis Question

Wed, 05 Jun 2013 16:39:56 GMT

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

Re: Sum and Aggr Set Analysis Question

sushil353 — Wed, 05 Jun 2013 16:43:01 GMT

What is the expression to calculate the variable?

Re: Sum and Aggr Set Analysis Question

Wed, 05 Jun 2013 16:44:43 GMT

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

Wed, 05 Jun 2013 17:11:02 GMT

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

Wed, 05 Jun 2013 17:20:06 GMT

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

Re: Sum and Aggr Set Analysis Question

Wed, 05 Jun 2013 18:00:54 GMT

What are the dimensions in the chart?

Re: Sum and Aggr Set Analysis Question

Wed, 05 Jun 2013 18:08:19 GMT

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

Wed, 05 Jun 2013 18:21:16 GMT

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

Wed, 05 Jun 2013 18:21:47 GMT

This still gives me 0 for everything.