4 Replies Latest reply on Jul 4, 2016 2:54 AM by kek soon yong

    YTD and month calculation problem

    kek soon yong

      Hi all,

       

      I need a help on YTD  and month calculation.

       

      This is my sample data, so basically i want to setup a month parameter, and when i select the month, the YTD sales will appear.

      Now it's only showing sales for the particular month. Thanks!

        • 1. Re: YTD and month calculation problem
          Bharath P

          Hi,

           

          Please find attached workbook with YTD values.

           

          I have done following modifications in your workbook:

          1) Click on the drop down button of SUM(Sales) and you will see "Quick Table Calculation" and then click on "Running Total".

          Since it is a window calculation, you need specify the way it should be computed. In this case, you need to use "Table (Down)" as shown below:

          2) Then create a calculated field "Rank" with the following calculation:

          RANK_UNIQUE(RUNNING_SUM(sum([Sales])),'desc')

          This assigns the rank for each and every value in the window.

          Drag "Rank" field into filter shelf and specify range 1 to 1 (Essentially selecting the latest record) and click on "Apply"

          Specify "Table (Down)" computation for "Rank" field as shown below:

           

          This will display the latest month data with YTD value.

           

          Hope this helps.

           

          -Bharath

          1 of 1 people found this helpful
          • 2. Re: YTD and month calculation problem
            kek soon yong

            Hi Bharath,

             

            Thanks for the help, now i got stuck again after adding a new dimension.

             

            Let's say now i want to only see the categories for month of May based on the same parameters selection, and then show the YTD sales.

            Is that possible?

            • 3. Re: YTD and month calculation problem
              Bharath P

              Hi,

               

              Please find attached workbook with the ask.

               

              The trick to get it is the way we compute the window calculations like running total & rank_unique.

              Click on Sum(Sales) drop down and then click on "Edit Table Calculation"

              The following window will pop up. Click on Running along drop down and select "Advanced" option.

              Once you click on advanced option the following window will pop up. Partitioning refer to the fields on which you want to do your partition and remaining fields should go to addressing side. In this case since the running sum should be calculated at each category level, we keep category on partitioning side and the rest of two fields are moved to addressing side.

              In the similar manner, you need to change the way "Rank" has been computed and then you will see following window:

              Finally filter the window using Rank = 1

               

              -Bharath

              1 of 1 people found this helpful
              • 4. Re: YTD and month calculation problem
                kek soon yong

                Hi Bharath,

                 

                thanks so much for the help!

                I also found out that i can use LOD (level of detail) calculation using below formula as it gives me the same result

                { FIXED