Create straight table with counts from the same dimension?

[#1]

Hi, all.

I'm using a Custom Dimension to create a Straight Table that shows specific counts from my database.

This is the heart of the expression:

IF([IVR_Payment Columns]='Payment Succeeded',

sum(AGGR(sum({<$(='['&[~tz]&' Date]')={">=$(=Vstartdate)<=$(=Venddate)"}, event={'Good Payment'}>} event_value),[Call Key],event_value))

,

IF([IVR_Payment Columns]='Payment Failed',

sum(AGGR(sum({<$(='['&[~tz]&' Date]')={">=$(=Vstartdate)<=$(=Venddate)"}, event={'Bad Payment'}>} event_value),[Call Key],event_value))

))

Looking at the raw data, I expect to see 10000 Succeeded, and 500 Failed.

When I view the report, I see this:

Payment Succeeded 10,000

Payment Failed         0

If I replace the second sum with the first, I get this:

Payment Succeeded 500

Payment Failed 0

I'm able to put several different event strings into the first expression to get their counts, but no matter what, I can't get anything to appear in the second expression.

However, if I put them in completely separate charts, they appear just fine.  Can I put multiple calculations across similar tables in a single Straight Table?

[swuehl · Accepted solution]

Try to add the NODISTINCT qualifier to the aggr() functions, like:

IF([IVR_Payment Columns]='Payment Succeeded',

sum(AGGR(NODISTINCT sum({<$(='['&[~tz]&' Date]')={">=$(=Vstartdate)<=$(=Venddate)"}, event={'Good Payment'}>} event_value),[Call Key],event_value))

,

IF([IVR_Payment Columns]='Payment Failed',

sum(AGGR(NODISTINCT sum({<$(='['&[~tz]&' Date]')={">=$(=Vstartdate)<=$(=Venddate)"}, event={'Bad Payment'}>} event_value),[Call Key],event_value))

))