2 Replies Latest reply: Sep 17, 2015 1:46 AM by ervin jafari RSS

    set expression help

    gerry castellino

      I'm trying to formulate a set expression that gives me the sum of sales for my QTD for my current year.

       

      =sum( {$<[Transaction Fiscal Year] = {$(=max([Transaction Fiscal Year]))},  

               [Transaction Fiscal Quarter] = {$(=max([Transaction Fiscal Quarter]))} >} sales  )

       

      I have to add a year modifier to get me the sales for the latest year/latest quarter, I've not structure this the right way.

       

      Gerry.

        • Re: set expression help
          Gysbert Wassenaar

          Perhaps like this:

           

          =sum( {$<[Transaction Fiscal Year] = {$(=max([Transaction Fiscal Year]))}, 

                   [Transaction Fiscal Quarter] = {$(=max({$<[Transaction Fiscal Year] = {$(=max([Transaction Fiscal Year]))}>}[Transaction Fiscal Quarter]))} >} sales  )

           

          If not, please post a small qlikview document that demonstrates the problem.

          • Re: set expression help
            ervin jafari

            I think you can create field "[Transaction Fiscal Year]&[Transaction Fiscal Quarter] as Transaction_Fiscal_YearQuarter "

            in your edit script, then set the variable that calculated the maximum YearQuarter like below

            V_MaxYearQuarter = max(Transaction_Fiscal_YearQuarter)

            if you want to calculated the max Year Quarter at all you can use "All" keyword :

            V_MaxYearQuarter = max(all Transaction_Fiscal_YearQuarter)

            at the end your expression should be like this

            sum({<Transaction_Fiscal_YearQuarter  = {$(V_MaxYearQuarter)}>}sales )