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