3 Replies Latest reply on Jul 27, 2016 11:58 PM by Matan Bracha

    Total row doesn't show "Sum"

    Matan Bracha

      Hi All!

      I have a crosstab, and I'm mixing in it data from 2 data sources.

      My dimensions are from source A, and I have a few measures that are calculations and the data comes from source B.

       

      Example:

      CASE ATTR([A].[Val])

      WHEN "abc" THEN ZN(SUM([B].[abc_Clicks]))

      WHEN "def" THEN ZN(SUM([B].[def_Clicks]))

      WHEN "ghi" THEN ZN(SUM([B].[ghi_Clicks]))

      ELSE 0

      END

       

      On the crosstab I can see the different values according to "Val", but in the "Total" row I have 0:

       

       

       

      Does anyone has any idea why I don't get a normal total?

      And how can I fix it to show the real values?

      Thanks a lot!!

        • 1. Re: Total row doesn't show "Sum"
          Michel Caissie

          Matan,

           

          When computing the Total tableau computes the calculated field without taking the granularity of the rows into account.

           

          So for the Total  CASE ATTR([A].[Val])   dont return  a single value and it jumps to ELSE 0  which is what you get in the Total.   

           

          What you can do is to compute the expected total directly in your calculated field

           

          CASE ATTR([A].[Val])

          WHEN "abc" THEN ZN(SUM([B].[abc_Clicks]))

          WHEN "def" THEN ZN(SUM([B].[def_Clicks]))

          WHEN "ghi" THEN ZN(SUM([B].[ghi_Clicks]))

          ELSE ZN(SUM([B].[abc_Clicks])) +  ZN(SUM([B].[def_Clicks])) + ZN(SUM([B].[ghi_Clicks]))

          END

           

           

          Michel

          • 2. Re: Total row doesn't show "Sum"
            Michel Caissie

            By the way, it's hard to see with only your screenshot, but if there is more than 3 values for A.Val, then you will need something like

             

            IF ATTR([A].[Val]) = ATTR([A].[Val])

            THEN

            CASE ATTR([A].[Val])

            WHEN "abc" THEN ZN(SUM([B].[abc_Clicks]))

            WHEN "def" THEN ZN(SUM([B].[def_Clicks]))

            WHEN "ghi" THEN ZN(SUM([B].[ghi_Clicks]))

            ELSE 0

            END

            ELSE ELSE ZN(SUM([B].[abc_Clicks])) +  ZN(SUM([B].[def_Clicks])) + ZN(SUM([B].[ghi_Clicks]))

            END

            • 3. Re: Total row doesn't show "Sum"
              Matan Bracha

              OK there's an update - I changed the "ATTR" into "MAX" and got *some* of the totals.

              In some of the rows I see the total and it's calculated as it should, and in the rest of the rows there's still zero...

               

              ***?!