maximum of sum(sales) of a year

[thanhphongle]

Hello Community,

this is my table

Date	Sales
01.01.2015	1000
01.07.2015	2000
01.09.2015	3000
01.01.2016	5000
01.04.2016	7000
01.01.2017	2000

how can I get

the biggest sum of sales per year?

QV should return me

12.000 € for the year 2016

I tried this expression. but it didnt work

=max(aggr(sum(Sales), year(Date)))

Could anyone help me out here?

[Anil_Babu_Samineni · Accepted solution]

Try this?

Make sure to create Year field in Script like and Be aware of Date format of your environment

Year(Date) as Year

Then Set analysis, May be this

Sum({<Year = {'$(=Max(Year-1))'}>}Sales)

[tresesco]

Create a Year field in the script like:

Load

          Year(Date) as Year

And then in the front-end try :  = max(aggr(sum(Sales), Year))

[thanhphongle]

it works, thank you ! but is there another possibility without changing the script?

[tresesco]

If even possible (I doubt though), would not be an easy one. Creating a year/month field in the script is very common fact in BI, so I would not even try to find an alternative in the front-end.

[c_gilbert]

= max(aggr(sum(Sales), Year(Date)))

[Ajaysantosh]

your suggest expression don't work it's hide all table data so please show me the correct expression

thank you