If Statement and Set analysis

[#1]

Hello,

I am trying to use if statement with set analysis but do not know how to do that.

What I am trying to do is to set up formula that gives mu actuals if month less than current month and forecast if month equal or more than curent month. Also I want the formula to ignore all user selection.

So I came up with the following formula:

sum(aggr(if(Month < $(VcurrentMonth),sum({1<Year = {$(=Year(today()))}>}(Units)),sum({1<Year = {$(=Year(today()))}>}(FC$))),Business_Level,[Demand Entities], Month,Quarter))  it works nice but I does not ignore the filters because of if statement.

Any ideas how to get that?

Any help would be appreciated.

Thanks

Denis

[MK_QSL]

sum(aggr(if(Month < $(VcurrentMonth),sum({1<Year = {$(=Year(today()))}, Business_Level = , [Demand Entities] = , Month = , Quarter = >}(Units)),sum({1<Year = {$(=Year(today()))}, Business_Level = , [Demand Entities] = , Month = , Quarter = >}(FC$))),Business_Level,[Demand Entities], Month,Quarter)) 

[#3]

Hi Manish,

Thank you for getting back.

The formula is still reacting on filters. I would like to prevent it to react on filters at all.

Like this formula :sum({1<Year = {$(=Year(today()))}>}BudgetDollars), but I do not know how to do it with if statement?

Thanks

Denis

[michael_anthony]

Try including the Outer Sum function with the set analysis also.  I believe the inner sum functions are applying no filters, but because your returning an aggr set of values by Month, Qlikview may be then applying filters to that internal table that the aggr returns.

eg.

sum( {1<Year = {$(=Year(today()))}>} aggr(if(Month < $(VcurrentMonth),sum({1<Year = {$(=Year(today()))}>}(Units)),sum({1<Year = {$(=Year(today()))}>}(FC$))),Business_Level,[Demand Entities], Month,Quarter))