Sum last three month sales and only maximum number in last 3 months

[Anonymous]

Hello all,

I need help with the following: Sum last three month sales and only maximum number in last 3 months.

based on the maximum version, i need to sum the sales amount.

Ex- If i selected 4th month, I will sum the values month 4 - sales 35 ,month 3- sales 20 and month 2 - sales 40. so finally i will get 95.

Thanks in advance.

month	version	sales
1	1	10
1	2	20
1	3	30
2	1	10
2	2	50
2	3	30
2	4	40
3	1	10
3	2	20
4	1	10
4	2	10
4	3	20
4	4	15
4	5	35
4	6	35

[devarasu07]

Hi,

Try like this in your expression

=FirstSortedValue(Distinct Aggr(Max(sales),month,version),-Aggr(Max(sales),month,version))

[Anil_Babu_Samineni]

May be this?

Max({<month = {'> $(=Max(month-3)) <=$(=Max(month))'}, version = {'<=$(=Max(version))'}>}sales)

[Anil_Babu_Samineni]

Or this?

Max({<month = {'> $(=Max(month-3)) <=$(=Max(month))'}, version = {'<=$(=FirstSortedValue(sales, Aggr(Max(version), version)))'}>}sales)

[Digvijay_Singh]

Like this -

Don't forget to change the total mode to Sum of rows.

[sunny_talwar]

Try this

=Sum({<month = {"$(='>=' & (Max(month) - 2) & '<=' & Max(month))"}>}Aggr(FirstSortedValue(sales, -version), month))

[sunny_talwar]

I think the OP is not looking to pick max sales, but sales associated with max version

[Digvijay_Singh]

Ya, missed that, this works -

Firstsortedvalue({<month={">=$(=Max(month)-2)<=$(=Max(month))"}>}sales,-version)

[Digvijay_Singh]

Updated one, missed version condition earlier..

[Anonymous]

The above expression shows only maximum values of the selected month only.