Hello Community,
I need to create a framework with Rappel that we assign to certain clients according to an annual agreement established for each one. To be able to track the amount we would have to pay each month of the year, I divided the amount of the annual agreement by the 12 months.
What I wanted now was to calculate the average sales as a function of the number of months elapsed until today, for that I used the following expression: SUM({ <Year = {$(=max(Year))}> }LtsSold) /Num(Month(Today())). However, when I select the month of January, for example, I can't know whether or not customers had to receive rappel. Because it will divide by 11 months.
On the other hand, if you use SUM({ <Year = {$(=max(Year))}> }LtsSold)/Count(distinct(Mes)) the values will be divided by the months in which customers bought which does not bring the information that I intend
Is there a way to calculate the average of effective sales of customers based on the number of months elapsed depending on the selection of months in the filters?
| Client |
Annual Agreement |
Agreement Monthly Average |
Sales to Date |
Months w/Sales |
Elapsed Months |
Eligible Rappel |
| A |
1,800 Lts |
150 Lts |
1,666 Lts |
11 |
11 |
Yes |
| B |
2,600 Lts |
216 Lts |
2,998 Lts |
10 |
11 |
Yes |
| C |
1,800 Lts |
150 Lts |
1,796 Lts |
8 |
11 |
Yes |
| D |
1,900 Lts |
158 Lts |
1,298 Lts |
7 |
11 |
No |
