1 Reply Latest reply: Jun 3, 2014 6:41 PM by Stefan Wühl RSS

    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?

        • Re: Create straight table with counts from the same dimension?
          Stefan Wühl

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


          ))