Counting Wins / Losses

[wmclaren]

Hi There,

 

I am new to Caspio and was hoping someone could help me.

 

I have compiled a table of results and in my last column I built a calculated field which produces an outcome of either Win or Loss.

 

At the bottom I am trying to create a COUNT formula for aggregation so I can build a percentage which shows the amount of wins as a percentage of the total count.

 

Is there a way to do this?

 

Thanks

Warwick

[Jan]

Hi Warwick,

 

I am sorry, can you write an example of the table and what do you want to get?

For example,

1 0 1 wins = 2

0 0 1 wins = 1

0 0 0 wins = 0

Result = 3/9*100 = 33.33%

Is it correct?

 

If yes, you can check the Formula in your Aggregation field and enter the formula like:

SUM/(COUNT*3)*100

 

Enter the number of your columns instead of 3.

 

Or your table is like

 

n n n 1 (means Win)

n n n 0 (means Loss)

n n n 1 (means Win)

Result = 2/3*100 = 66.67%

Is it correct?

 

If yes, you can check the Formula in your Aggregation field and enter the formula like:

SUM/COUNT*100

 

I hope it helps.

[wmclaren]

Hi Jan,

 

Your second version is what I have replicated. In inputted your formula SUM/COUNT*100 in the formula aggregation but the value is showing as 0.00

 

I checked to make sure that the basic Count and SUM are working and they are so not sure why its not calculating the two together to give me a percentage of the overall count.

[Jan]

Hi Warwick,

 

Sorry, I have several question to understand better what is wrong.

Do you use type Number for all fields that are used in the Calculation?

Do you use "1" for Wins and "0" for "Losses"?

Do all cells in the column Wins have values?

 

If you can post the link to your DataPage, it will be great

[Aether]

Hi @wmclaren, 

For this workflow, I suggest that you use a calculated field to determine if the value is a win or loss.

Example:

CASE 
WHEN [@field:FieldName] = 'Win'
THEN 1
ELSE 0
END

Then use the that calculated field in the Totals and Aggregation, and use AVG() to get the percentage of that. 

I hope this helps.

~WatashiwaJin~ 

[Aether]

You can also refer to this following documentation:

https://howto.caspio.com/datapages/reports/advanced-reporting/totals-and-aggregations/

https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

https://howto.caspio.com/function-reference/