2 Replies Latest reply on Nov 20, 2017 10:36 AM by Shengdu HUANG

    How can i make a pie chart with Table Calculation?

    Shengdu HUANG

      Dear all,

       

      I am working for audit company and now i am trying to create a slide in order to assess the Benford's law (Benford's law - Wikipedia) for one client's account.

       

      So my idea is :

      Create a calculated field "Benford percentage" which shows the percentage of each number: LOG(1+1/int([Benford]))

       

      Create another calculated field "Regulation" (Table Calculation) which shows if the benford's law is OK to a number or not:

      if SUM([Number of Records])/ TOTAL(SUM([Number of Records])) <AVG([Benford pourcentage])*0.9

      or SUM([Number of Records])/ TOTAL(SUM([Number of Records])) >AVG([Benford pourcentage])*1.1 then "KO"

      Else "OK"

      END

       

      With these fields i have finished a bar chart.

       

      Now i want to have another pie chart to show the number "OK" vs "KO". For example, in testing the first number we have 1 to 9, if we get 6 "OK" and 3 "KO, here is the pie chart i want:

      Capture.PNG

      But the diffculty is from my calculated field "Regulation" because it is made by Table Calculation.

       

      Anyone has met in this situation? You can find my .twbx from attachement.

       

      Thanks in advance,

       

      Regards,

       

      Shengdu

        • 1. Re: How can i make a pie chart with Table Calculation?
          Asher Campbell

          There are two possible approaches to creating the desired pie chart.

           

          Option 1: Nested Table Calculations

           

          In the attached workbook, I created a new worksheet that is what I understand the desired result.

           

          This is accomplished using the calculation:

           

          IF INDEX() = 1 THEN WINDOW_SUM(IF [Regulation] = "OK" THEN 1 END) ELSE WINDOW_SUM(IF [Regulation] = "KO" THEN 1 END) END

           

          This looks complex, but can be thought of as doing the following....

           

          For the first mark in the partition, sum up the number of marks where the value of Regulation is "OK", for all other marks sum up the number of marks where the value of Regulation is "KO"

           

          We then filter using INDEX() to only show 2 marks, one for OK, and one for KO.  From there, the view construction is straight forward.  No dimensions on rows or columns, measure on angle.  Because our "measure" in this case relies on the nested table calculations, we need the dimensions to all be on detail.

           

          Option 2:  LODs

           

          The desired analysis can be accomplished by writing LODs for the percent of total calculations rather than using table calcs.  Then using that percent of total to write an LOD that calculated OK or KO at the [Benford] level of detail.  You would simply use that calculated dimension and COUNTD([Benford]) as the measure to create the pie chart.

          • 2. Re: How can i make a pie chart with Table Calculation?
            Shengdu HUANG

            Thanks exact what i want!!!

             

            Sorry for correction answer quite late