3 Replies Latest reply: May 6, 2010 8:50 AM by matthew.fryer

# MAX Aggregation & Function

Hi all,

I'm just a beginner in QV. Hope anyone could help me on this..

Currenlty, I'm trying to create a graph that comparing Current Year Sales & Last Year Sales.

Knowing that i need to create 2 expression for this chart. I've 3 years data which is 2007, 2008 and 2009.

For example, if you select '2008' , the graph will show sales amount for 2008 & 2009.

I can get the result by using set analysis, but i'm wonder which expression is more suitable for my graph for Last Year Sales? With or wihout MAX function?

a. Sum({\$<[Invoice Year]={\$(=([Invoice Year]-1))}>} [Sales Amount])

OR

b. Sum({\$<[Invoice Year]={\$(=MAX([Invoice Year]-1))}>} [Sales Amount])

(The Max here should be aggregation or function?)

Although i've read thru the Max Aggregation & Function in Reference Manual but stil not very clear.

Appreciate anyone can clarify on this.

Thanks,

Phoebe Yaw

• ###### MAX Aggregation & Function

If you select a specific year, there should be no difference. Only one year is selected, so the max([Invoice Year]) equals the [Invoice Year]. The difference happens when you select no year or more than one year. The expression without the max() will then return nothing. The max([Invoice Year]) will give you data based on the most recent Invoice Year if none are selected, or on the maximum selected year if you've made multiple selections.

• ###### MAX Aggregation & Function

I can understand better now.

~Phoebe

• ###### MAX Aggregation & Function

If you take a look at the "only()" function it should do what you want. It will return the year only if you have a single value selected/possible.So your 2 expressions would be something like:

Sum({\$<[Invoice Year]={\$(=only([Invoice Year]))}>} [Sales Amount])

Sum({\$<[Invoice Year]={\$(=MAX([Invoice Year]-1))}>} [Sales Amount])

The user can pick any year and it will compare that year and the previous. You can set the calculation condition and text to check only() is returning a value also and prompt the user to select a year.