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

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


      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.


      Phoebe Yaw



        • MAX Aggregation & Function
          John Witherspoon

          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

            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.