Skip to main content

Number Element Limitations

  • January 26, 2023
  • 3 replies
  • 41 views
MichaelSargent
Forum|alt.badge.img+14

I have a base where one of my tables is transactions (primary column) where there is a revenue column, date column, and other pertinent information. If I wanted to view revenue by month, that's super simple in views and I could see the average at the top if I changed sum to average. All fine and dandy but if I wanted average monthly revenue in a number element in Interfaces, there seems to be no way of doing that easily. I feel like a grouping or bucket function in the number element settings would make perfect sense but the best I can do now is average revenue per transaction and that's not what I want. Any suggestions welcome.

3 replies

bruceconsulting
Forum|alt.badge.img+13

Do you have a field in your table for "Month"?  I have used a formula field to populate this field in the past in order to get the chart I want in Interfaces.  Here's the formula:

IF(MONTH({Sale Date})=1,"Jan",IF(MONTH({Sale Date})=2,"Feb",IF(MONTH({Sale Date})=3,"Mar",IF(MONTH({Sale Date})=4,"Apr",IF(MONTH({Sale Date})=5,"May",IF(MONTH({Sale Date})=6,"Jun",IF(MONTH({Sale Date})=7,"Jul",IF(MONTH({Sale Date})=8,"Aug",IF(MONTH({Sale Date})=9,"Sep",IF(MONTH({Sale Date})=10,"Oct",IF(MONTH({Sale Date})=11,"Nov","12")))))))))))

If you need any further help, please feel free to schedule some time with me.  Here is my Calendly link: https://calendly.com/bruce_consulting/30min
MichaelSargent
Forum|alt.badge.img+14
  • MichaelSargent
  • Author
  • Inspiring
  • February 2, 2023

Do you have a field in your table for "Month"?  I have used a formula field to populate this field in the past in order to get the chart I want in Interfaces.  Here's the formula:

IF(MONTH({Sale Date})=1,"Jan",IF(MONTH({Sale Date})=2,"Feb",IF(MONTH({Sale Date})=3,"Mar",IF(MONTH({Sale Date})=4,"Apr",IF(MONTH({Sale Date})=5,"May",IF(MONTH({Sale Date})=6,"Jun",IF(MONTH({Sale Date})=7,"Jul",IF(MONTH({Sale Date})=8,"Aug",IF(MONTH({Sale Date})=9,"Sep",IF(MONTH({Sale Date})=10,"Oct",IF(MONTH({Sale Date})=11,"Nov","12")))))))))))

If you need any further help, please feel free to schedule some time with me.  Here is my Calendly link: https://calendly.com/bruce_consulting/30min

Yes, I do have a month column but that doesn't help with the number element since you can't group. For instance, if there was 5 transaction in Jan and 6 transactions in Feb, and you wanted to know the average sales volume over both January and February, you wouldn't be able to get the number element to provide that for you. It could only provide an average over the 11 transactions.

MichaelSargent
Forum|alt.badge.img+14
  • MichaelSargent
  • Author
  • Inspiring
  • February 2, 2023

Yes, I do have a month column but that doesn't help with the number element since you can't group. For instance, if there was 5 transaction in Jan and 6 transactions in Feb, and you wanted to know the average sales volume over both January and February, you wouldn't be able to get the number element to provide that for you. It could only provide an average over the 11 transactions.


The only solution I could come up with, which is frankly ridiculous, is creating another table with rollup fields for every month.

Terms & ConditionsAccessibility statement