Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create straight table with counts from the same dimension?

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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


))

View solution in original post

1 Reply
swuehl
MVP
MVP

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


))