Summary Report For Many Yes/no Fields

[DataCobalt]

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!

[Xiang]

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!

[DataCobalt]

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!

[Xiang]

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!

[DataCobalt]

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!