Using IF or set analysis inside AGGR function?

Fri, 04 Apr 2014 09:46:21 GMT

tl;dr : How to write an expression that has IF condition inside an AGGR function, i.e.

SUM(AGGR(IF(SUM(field1)=0,only({1}IF(field2 = 60,1000))),field2))?

Hello

I'm trying to add a row to my table if certain conditions are met (namely when there's no existing data and customer's lifetime is, say, 60 days). This works in a table that has customer lifetime as a dimension, but it fails when I remove the dimension and add it to the expression using AGGR function.

Example

I have a table with one dimension cohort_age and three columns : some_values, add_value_if and combined. The first one (some_values) is simply a sum of existing values. The second one (add_value_if) adds a row when I don't have data for a given lifetime. And the third one combines these two into one time series using & operator. It works and looks like this :

When I remove the cohort_age dimension and add it into the expression using AGGR function things start to fall apart.

Working Expressions (in a table with explicit dimension)

some_values : sum(duu)

add_value_if : IF(sum(duu)=0,only({1}IF(cohort_age = 60,1000)))

combined : SUM(duu) & IF(sum(duu)=0,only({1}IF(cohort_age = 60,1000)))

Failing expression (in a table with implicit dimension)

add_value_if:

SUM(AGGR(
IF(sum(duu)=0,only({1}IF(cohort_age = 60,1000)))
,cohort_age))

combined :

SUM(AGGR(
SUM(duu)
&
IF(sum(duu)=0,only({1}IF(cohort_age = 60,1000)))
,cohort_age))

I also tried to use set analysis -- only({1 <cohort_age = 60, cohort_age={"=SUM(duu)=0"}>} 1000 ) -- but the results weren't any different. So I'd much appreciate if you guys could point me to the right direction 🙂

Best

Joonas

topic Using IF or set analysis inside AGGR function? in QlikView

Using IF or set analysis inside AGGR function?