Show the total monthly sales per user

Ask for Help
1

Basically, I want to show the monthly sales per user, not the total of all sales per user.

I have an array which brings the months of the sales per user, but I only want to show the current month (“which is 4”) removing the other months (in this case it only appears the “3” but I don’t want to show any of them except the current)

Maybe I’m doing it the wrong way.

image
image1369×184 49.8 KB

2

First, use the math or format date column to convert sales dates and search dates to MMYYYY format, then IF-ELSE column to find matching sales, then use rollup to sum them. you can add user email to formatted dates, to extract specific users.

3

Thanks for the reply! Ill try that. I always struggle with dates

4

Expanding on Uzo’s idea:

  • Create a math column in your User Profiles table:

YEAR(D)*100+MONTH(D)

With D being the “now” value. You would get 202304 for today.

  • Create a math column in your sales record table.

YEAR(D)*100 + MONTH(D)

WIth D being the date of the sales record.

  • Create a relation from the user profiles table to the record table, make it match multiple rows, then rollup the sales value.
3 Likes
5

How would this math formula change when for looking at prior month vs current month

YEAR(D)*100+MONTH(D)-1?

6

You could use Year(Date)*12 + Month(Date), and then subtract 1 from that for the previous month.

1 Like
7

That will fail at the year boundaries.

8

(YEAR(D)*12)+(MONTH(D)-1)

D = Now

Using this, the value that shows is 24281. Is it supposed to show 202305?

9

No. It doesn’t give a “human readable” result, but it’s useful when you need to add/subtract months.

10

Ok thanks! as long as it works, thats all that matters!

So essentially next month it will show 24282?

11

24282 is June 2023
Next month (July) will be 24283 :slightly_smiling_face:

1 Like