PERCENTAGE

Anthony.Kariuki

Forums

I am looking for a way to calculate percentages. See the sample data below. I have summarized clerks based on their transactions using criteria called the use of cards =denoted as 1 or no card used denoted as 0.  I need a way of giving percentages based on the clerk's total number of records. for example below Alice  (302/319)*100 Also how to do a total summary of cards used.

 
See the below the expected result as done manually in excel
 
 

Brian Element Wed, 01/18/2023 - 08:36

There are two ways to do this.  The first way is using a virtual field but it only can be done if the file is in order by Clerk Name in this instance and that there is only two options.  If there are more than two options this won't work.  In this scenario you would create a virtual numeric field and use the following equation:

@if(CLERK_NAME = @GetPreviousValue("CLERK_NAME"), NO_OF_RECS1 / (NO_OF_RECS1 + @GetPreviousValue("NO_OF_RECS1")), @if(CLERK_NAME = @GetNextValue("CLERK_NAME"), NO_OF_RECS1 / (NO_OF_RECS1 + @GetNextValue("NO_OF_RECS1")), 0))

This will give you the following result:

You will notice that Dorcas doesn't work because the names are different.  The names have to be exact for this to work.

The other way to do this has a few more steps but isn't limited to only two items.

The first step would be to do a summary by Clerk Name so that you have the total records for each clerk.

I renamed the NO_OF_RECS_SUM to TOTAL_RECS

Then join this back to the original file using the Clerk Name as the match and only selecting the Total Recs field in the field list for the secondary database.

This will give you the following file:

Then add on a virtual field to calculate the percentage using the following formula:

NO_OF_RECS1 / TOTAL_RECS

Anthony.Kariuki Thu, 01/19/2023 - 07:00

Thanks Brian. Much appreciated. The first solution works and yes Dorcas should have been the same.   However, the second solution does not take care of where a clerk did not use any card (In the case of Moses). The expected result is that he is at 0% as in the first solution. 
If the sample data above was for a city say Nairobi -and I wanted to report the average card usability(Card Use=1) as a block-figure say 94% how would I go about it? average of control total/All the card use / No Of clerks

Brian Element Fri, 01/20/2023 - 07:18

Hi Anthony,

For the Moses you could add an @if statement in the percentage checking if the No of Recs is the same as the Total of recs and if Card Use is 0, then 0 or else 1, depending on how you want to report it.

For the second part probably do a summary on 0 and 1 with percentages selected and this will give you the percentage of card use versus non-card use.