topic Re: maximum of sum(sales) of a year in QlikView

maximum of sum(sales) of a year

thanhphongle — Fri, 19 May 2017 11:15:23 GMT

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?

Re: maximum of sum(sales) of a year

Anil_Babu_Samineni — Fri, 19 May 2017 11:17:55 GMT

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)

Re: maximum of sum(sales) of a year

tresB — Fri, 19 May 2017 11:19:24 GMT

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))

Re: maximum of sum(sales) of a year

thanhphongle — Fri, 19 May 2017 11:35:21 GMT

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

Re: maximum of sum(sales) of a year

tresB — Fri, 19 May 2017 12:00:27 GMT

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.

Re: maximum of sum(sales) of a year

c_gilbert — Fri, 19 May 2017 12:52:29 GMT

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

Re: maximum of sum(sales) of a year

Ajaysantosh — Tue, 11 Aug 2020 12:13:16 GMT

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

thank you