topic Re: If Statement and Set analysis in QlikView

If Statement and Set analysis

Fri, 07 Nov 2014 22:34:44 GMT

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

Re: If Statement and Set analysis

MK_QSL — Sat, 08 Nov 2014 00:42:36 GMT

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

Re: If Statement and Set analysis

Sun, 09 Nov 2014 17:44:44 GMT

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

Re: If Statement and Set analysis

michael_anthony — Mon, 10 Nov 2014 03:38:55 GMT

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