Count number of fields containing zero

John Halls thanks for the reply and the information. I hope some background will help find a better way 

These figures and fields are in a dashboard that Nioxus helped me set up. We did it pretty quickly over zoom without him being able to see my DB. If he had had a thorough understanding of my DB, he probably would have come up with a different solution, but this is what I have for now.

Each “This Week” field is a formula field that sums up the miles gathered from selected records. The way it is set up, I can easily look up the miles per year, miles per quarter, miles per month, or miles per week, then compare them to similar times in the past, although I haven’t build it out that far, as of yet. At this point I am working out the FiscalWeek comparisons, then I will use that information to build out the other comparisons.

I understand how to sum the values in a column of records and I already do that for the table ‘Mileage Pay’: sum(‘total miles’). However, that only allows me to sum or avg, ALL of the ‘total miles’. (‘total miles’ is the total miles for each load)

The purpose here, is to have a dashboard to compare totals, averages, etc. from any year, quarter, month, week, etc. In this case, I have looked up FiscalWeek totals. With that information, I would like to see the avg of the past 4 weeks, 6 weeks, and 13 weeks. Which I can easily do (and currently do), except, the averages include weeks that I was on Hometime, which are zero paid miles. I actually like to include the zero miles most of the time, because it allows me to see how much Hometime effects the miles I get. However, I would also like to see the avg of x weeks, excluding the weeks that I was on Hometime. This would allow me to compare to what my employer sees as my average paid miles.

The formula that I have for ‘This Week’ is:

let xYear := number(text(year('End of Current Pay Period (next Monday)')));
let xThisWeek := week(now());
sum((select 'Mileage Pay')[Year = xYear and 'FiscalWeek of the Year' = xThisWeek].'Total Miles')

Where ‘End of Current Pay Period (next Monday)’ is a date field where I enter the date.

Where ‘Mileage Pay’ is a Subtable of ‘Load Information’. ‘Mileage Pay’ contains the paid miles for each load.

Where ‘FiscalWeek of the Year’ is a field set up in the ‘Mileage Table’  to help look up the FiscalWeek of the Year. The other fields are ‘Year’, ‘Quarter of the Year’, and ‘Month of the Year’.

The formula for the field ‘FiscalWeek of the Year’ is:

let tempDate := year('Pay Period End');
let offset := (select FiscalYear)['Calendar Year' = tempDate].'Offset Days';
let offsetN := number(offset);
let FiscalWeek := week('Pay Period End' - offsetN);
format(number(FiscalWeek), "00")

Which returns the appropriate ‘FiscalWeek of the Year’.

I apologize for the long explanation, but perhaps it will lead to a better solution.

By the way, this database is for my own personal use and is the only database I have ever constructed. I migrated to Ninox after using a spreadsheet, which proved inadequate after the first year. All of this, to my regret, is being done on an iPad.

There are currently 271 records in the ‘Mileage Pay’ table.

Thanks for your help,

Chris

Fred Actually, I’m not tracking my Hometime. I just know that I must have been on Hometime, if the following pay period has zero miles.

It’s a good idea, Fred! I will try to figure out a way to track my Hometime.