CODEWITHSUNDEEP
amazon-web-servicesamazon-quicksight
S. R.
S. R.

Reputation: 61

Merging 2 working calcs generate an aggregation error

I've made 2 distinct parameters:

1)

ifelse((difference(sum({hc_agg_total} * 1),[{date_month} DESC],1,[membership,{country_allocation},campus,customer,{customer_since},{customer_horizon}])*-1)+sum({hc_agg_total})>sum({hc_agg_total}),sum({hc_agg_total}),(difference(sum({c_agg_total} * 1),[{date_month} DESC],1,[membership,{country_allocation},campus,customer,{customer_since},{customer_horizon}])*-1)+sum({hc_agg_total}))

and 2)

ifelse(month=1 AND {date_month}>{customer_since},{hc_agg_total},0)

I need to merge them together, because first one don't consider values for january, and second is its fix. So basically something like substitute the 0 in the 2nd with all the first, but I get an aggregate error, even if I put hc_agg_total around sum() too. This is what I've tried:

ifelse(month=1 AND {date_month}>{customer_since},sum({hc_agg_total}),ifelse((difference(sum({hc_agg_total} * 1),[{date_month} DESC],1,[membership,{country_allocation},campus,customer,{customer_since},{customer_horizon}])*-1)+sum({hc_agg_total})>sum({hc_agg_total}),sum({hc_agg_total}),(difference(sum({c_agg_total} * 1),[{date_month} DESC],1,[membership,{country_allocation},campus,customer,{customer_since},{customer_horizon}])*-1)+sum({hc_agg_total})))

the error is always the same and seems located around sum( but i can't guess why

Upvotes: 0

Views: 30

Answers (1)

kielni
kielni

Reputation: 5009

The ifelse syntax is

ifelse(
  if-expression-1,
  then-expression-1 
  [, if-expression-n, then-expression-n ...],
  else-expression
)

It's easier to see what's happening if you format your calculations so it's not a giant block of text:

ifelse(
  # if-expression-1
  month=1 AND {date_month} > {customer_since},
  # then-expression-1
  sum({hc_agg_total}),
  # else-expression-1
  ifelse(
    # if-expression-2
    (
      difference(
        sum({hc_agg_total} * 1),
        [{date_month} DESC],
        1,
        [
          membership, {country_allocation}, campus, customer, {customer_since},
          {customer_horizon}
        ]
      )
      * -1
    )
    # are you intending to add sum({hc_agg_total}) to the difference?
    # if so, why are you comparing it to itself?
    # if not, the + should be a ,
    + sum({hc_agg_total}) > sum({hc_agg_total}),
    sum({hc_agg_total}),
    (
      difference(
        sum({c_agg_total} * 1),
        [
          {date_month} DESC], 1, [membership, {country_allocation}, campus,
          customer, {customer_since}, {customer_horizon}
        ]
      )
      * -1
    )
    +
    sum({hc_agg_total})
  )
)

Upvotes: 1

Related Questions