4 Replies Latest reply: Dec 13, 2012 5:15 AM by Siva Dandu RSS

    Calculation of YTD on cumulative fiscal month based on Month selection?

    Siva Dandu

      Hello Friends,

       

       

      I worked direcly with fsmonth column in the following example, It gives the currect values. ( See the text box : "Expression using fsmonth")

      But when iam trying to achieve the same results with MonthName field, it gives different results. (See the text box: "Requirement")

       

      MonthName, fsmonth

      Jan, 3

      Feb,1

      Mar, 2

       

      Requirement:

      If the user selects MonthName- Jan, The Requirement text box should show the sum(Sales) for all the months ( because fsmonth is 3 for January)

      -- Ex:  #sum(sales) where fsmonth<=3

       

      If the user selects MonthName=Feb, The Requirement text box should show the Sum(Sales) for Feb only( Because fsmonth is 1 for February)

      --Ex: Sum(Sales) where fsmonth<=1

       

      If the user selects MonthName=Mar, The Requirement text box should show the Sum(Sales) for Feb  & March ( Because fsmonth is 2 for March)

      --Ex: Sum(Sales) where fsmonth <=2

       

      Please help me to correct the expression in the Requirement Text box:

       

       

      Regards

      Siva

        • Re: Calculation of YTD on cumulative fiscal month based on Month selection?
          Siva Dandu

          Thanks a lot for your quick response.

           

          fsmonth will not be available on dashboard to make selections on fsmonth.

          The only selected fields are Year and MonthName.

           

          if i select MonthName, it should internally map to fsmonth value and this is going input  for the fsmonth in Set Analysis.

           

          For Ex:

          =sum({$<fsmonth={"<=$(=fsmonth)"}>}  sales) & ' Sum of Sales'

           

          Instead of using fsmonth(Underlined in the above statement), can we get this value based on selection made in list box of "MonthName".

           

          Expecting the results like this ( for your reference):

          If i select  year=2010,MonthName=Jan   --> The associated value is 3 for the this selected month(Jan'2010)

          i want the sum(Sales) according to fsmonth <=3 ( I mean Cumulative value :  10+20+5+20+30+30+40)

           

          If i select  year=2010,MonthName=Feb   --> The associated value is 1 for the this selected month(Feb'2010)

          i want the sum(Sales) according to fsmonth <=1 ( I mean Cumulative value :  20+30)

           

          If i select  year=2010,MonthName=Mar  --> The associated value is 2 for the this selected month(Mar''2010)

          i want the sum(Sales) according to fsmonth <=2 ( I mean Cumulative value :  20+30+30+40)

           

          In real time scenario, we need to count the sum(sales) according the financial year(Apr to Mar)

          The fsmonth value for Apr is 1, May is 2 ......Mar is 12

           

           

          if user selects Apr from Monthname, i want to get the results sum(sales) for the month of Apr

          if user selects MAY from Monthname, i want to get the results sum(sales) for the month of Apr and May.

          if user selects Jun from monthname, i want to get the results sum(sales) for the month Apr, May and Jun.

          .....

           

          This expression is NOT working properly in the "Requirement" text box

          =sum({$<year={"$(=GetFieldSelections(year))"},fsmonth={"<=$(=dual(fsmonth,'$(=GetFieldSelections(MonthName))'))"}>}sales)

           

          Iam not sure, whether the above expression is correct. Please correct me, if i am wrong?

           

          Hope this helps you to understand my requirement....

           

          Regards

          Siva

          • Re: Calculation of YTD on cumulative fiscal month based on Month selection?

            Hi,

             

            I found solution using set analysis, use this expression:

             

            =sum({$<year={"$(=GetFieldSelections(year))"},fsmonth={"<=$(=dual(fsmonth,'$(=GetFieldSelections(MonthName))'))"},MonthName = > } sales

             

            Niranjan M.