• 0

# Summary Report For Many Yes/no Fields

## Question

Good morning all,

I am running in to a bit of difficulty with the reporting and charting features of Caspio right now.

I currently have a table that has 21 Yes/No fields that are based on a review process. We would like to have a summary that shows either a count or % of No's in a given month (going off the date created field.

Now while an aggregation SHOULD work, it will only allow count, and not even of only No values and it will only allow 5 at a given time and we have 21 fields.

Charting has run into the same issue.

I can make a calculated field that pulls a count of only no values per field, but I haven't found a good way to make this calculation apply to only dates in a given date roll up month (I do really like the date roll up grouping) without having to manually apply date restrictions and even then it would only work if I wanted to pull one month at a time.

I am hoping others have run into similar issues with Caspio's aggregation and charting and have found workarounds. If anyone is creative it is this community!

Any an all help would be much appreciated.

Thank you!

## Recommended Posts

• 0

Hi DataCobalt,

How are you?

I think, you can use this formula in your Aggregation field:

`Count(case when FIELDNAME=0 then 1 else NULL end)`

Or this formula to calculate the percentage:

`100*Count(case when FIELDNAME=0 then 1 else NULL end)/Count`

I'll be grateful, if you tell me if the code works.

Have a nice day!

##### Share on other sites

• 0

That code should work great from what I can see, but the thing is that I have 21 of such fields and there are a maximum of 5 aggregations on a given report. So I am more looking at a way to get around that.

Thank you for the nicely formatted code though!

##### Share on other sites

• 0

Hi DataCobalt,

I've investigated how it works, and I've found the following formula:

`100*Count(case when [@field:@current]=0 then 1 else NULL end)/Count`

You can use "[@field:@current]" instead of the current field.

Have a nice day!

##### Share on other sites

• 0

Hi DataCobalt,

I've investigated how it works, and I've found the following formula:

`100*Count(case when [@field:@current]=0 then 1 else NULL end)/Count`

You can use "[@field:@current]" instead of the current field.

Have a nice day!

I am curious, what does this change? I will give it a shot though, thanks!

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.