Year to date calculations (min/max/median/sume/avg)

[svm]

Hi All,

I have an excel sheet with the following columns:

Date

Team

Sales Number

As numbers also retrospectively change (e.g. sales are returned etc), I need to calculate every month all year to date (i.e. Jan, Jan. Feb, Jan-Mar, Jan-Apr, Jan-May etc) numbers (i.e. sum of all sales per team, median of all sales per team, avg of all sales per team, min sale per team and max sale per team) from scratch.

Any ideal how I can do that.

Thanks, svm

[Kenda · Accepted solution]

Hey @svm!

I would recommend using a Filter tool with the following expression:

[Date]>tostring(tonumber(DateTimeYear(DateTimeNow()))-1) + "12-31"
&&
[Date]<=DateTimeToday()

This will keep only the records that have occurred so far this year. Using the T output, all you need then is a Summarize tool. Group by Team and then select all the calculations you need for the sales numbers.