topic MAX Aggregation & Function in QlikView

MAX Aggregation & Function

Thu, 08 Apr 2010 10:17:56 GMT

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

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

johnw — Fri, 09 Apr 2010 01:47:35 GMT

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

Fri, 09 Apr 2010 03:48:41 GMT

Thanks for the reply!

I can understand better now.

~Phoebe

MAX Aggregation & Function

Thu, 06 May 2010 12:50:01 GMT

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.