21 Replies Latest reply: Apr 5, 2012 1:47 AM by anantneo RSS

    Limiting data using Only-function

    Ville Syrjänen
      Hi,
      I have sales data that I'm trying to limit in table/chart using Only-function in Expression.
      Sum
      ({$<[YEAR]={$(=Only([YEAR])) }>} Sales)
      Now I'm trying to limit the data also with month, but without any success. Is it possible to limit things using Only-function several times like this?
      Sum
      ( if ([YEAR]= [YEAR]-1, {$<[MONTH]={$(=Only([AsOfMonth])) }>} Sales )
      I know, this does not work. The idea here is to limit year to be last year and month should be selected.
      Second question: I shoul also calculate last 12 months sales from same table. There is date, month, year etc available. What would be the best way to do that?

       

      Here is the sample report.

        • Re: Limiting data using Only-function
          Deepak Vadithala

          Hi,

           

          I had very similar requirement sometime back. And I had used variables to get this working. I had created some variables like this...

           

          vCurrentYear                  :          Max({1} Year)

          vPreviouYear                  :          Max({1} Year) - 1

           

          vCurrentMonth                :          Max({1<Year = {$(=$(vMaxYear))}>} MonthNumber)

          vPreviouMonthLastYear     :          IF($(vCurrentMonthNumber) = 1, 12, $(vCurrentMonthNumber) - 1)

           

           

          I had declared the variables in the load script and everytime I load the data...variables will be updated. Then I had variables within my expression and calculated the sales data for the above time periods.

           

          I hope this helps!

           

          Good luck.

           

          Cheers - DV