1 Reply Latest reply: Jun 20, 2016 4:39 PM by John Witherspoon RSS

    Total units previous Period

    Frank Buffay

      I have a requirement where I need to create a Current period and Previous period showcase on my App.

       

      For Total Units for Selected Period I`m using =sum(Units)

       

      The Select Period is derived from Fiscal Year & Quarters..

       

      So based on what my user would select as Currently period, I`d also want to show the same Data for previous period.

       

      IS this what I need to do ?

       

       

      set vPriorMonthYear = =Date(addmonths(max([Month Year]), -1), 'MMM-YYYY');

       

      And then the expressions for the current month and prior month columns is:

       

      Sum({<[Month Year]={'$(vPriorMonthYear)'},Year=,Month=,Date=>}Units)


      Can someone help me with this ??


        • Re: Total units previous Period
          John Witherspoon

          One possibility is to generate an AsOf table:

           

          AsOf Month Year,Month Type,Month Year

          Apr-2016,Selected,Apr-2016

          Apr-2016,Previous,Mar-2016

          Mar-2016,Selected,Mar-2016
          Mar-2016,Previous,Feb-2016

           

          Use Month Type as a dimension in your chart, sum(Units) as your expression. No variables, no set analysis, all complexity in the script instead of the chart, which is where I prefer to keep it when practical.

           

          This may not be the right solution for your specific requirement. Like the chart I suggest doesn't make it easy to then, say, add the difference in units between the months as another column. You might handle that by removing the Month Type dimension and having three expressions:

           

          Selected = sum({<[Month Type]={'Selected'}>} Units)
          Previous = sum({<[Month Type]={'Previous'}>} Units)
          Difference = Selected - Previous

           

          Or you could go nuts and handle that in script too:

           

          AsOf Month Year,Month Type,Month Year,Sign
          Apr-2016,Selected,Apr-2016,+1
          Apr-2016,Previous,Mar-2016,+1
          Apr-2016,Difference,Apr-2016,+1
          Apr-2016,Difference,Mar-2016,-1

           

          Add the Month Type back in as a dimension, and change the expression to:

           

          sum(Sign * Units)

           

          I probably wouldn't go that far, but maybe I would. It would depend on the situation I guess.