Discrepancy in Filters

nicole.turner
nicole.turner ✭✭
edited 02/28/23 in Formulas and Functions

Hi there!

I have a smartsheet where I am trying to report on numbers by quarter, and then numbers by total year, as well as some other filters. However, the numbers are just NOT adding up.

I have tried both by filtering through a report and filtering through the smartsheet itself.

E.g. I have run individual reports on Q1, Q2, Q3 and final Q YTD. When I filter these dates (I use 'date requested', is between [day before Q starts - day after Q ends]) I end up with:

Q1 465 (31Mar22-1Jul22)

Q2 494 (30Jun22-1Oct22)

Q3 509 (30Sept22-1Jan23)

Q4 YTD 249 (31Dec22-1stMar23)

However, when I filter for the whole YTD (I use 'date requested', is between [day before Q1 starts 31Mar22- day after Q4 YTD 1Mar23]), the total adds up to 1683. By my calculation given the figures above, the total should be 1717. Why is there a discrepancy? Am I being incredibly dim?

Answers

  • Kelly P.
    Kelly P. ✭✭✭✭✭✭

    @nicole.turner

    Perhaps your formula is double counting some cases? There are 34 extra cases when totaling the quarterly numbers which implies that the operators in your formula might need adjusting so that the "edge" dates are not captured in two quarters.

    Hope this helps!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!