3 Replies Latest reply on Jan 30, 2017 9:10 AM by dipti.patel.2

    Displaying table kinda cross tab and calculation help

    dipti.patel.2

      Hi,

      Questions -

       

      1. How do i show a table like this ?

       

      What i have is like this -

      I need to show the 'Total Execution' for Current Year and Prior Year and FSIS Allocation only for current year. This value is not broken down at the month level in data its only at year level, that is y its showing null as month.

      But how do i show the table format as in pic and not show NULL for month as allocation is not done monthly, but its only at year level.

       

      2. How do i compute the value Allocation which is at year level to be broken down at month level starting at Oct

      So Oct it wud be = (Current allocation / 12) = 2500

      Then Nov = 2500+2500 = 5000

      Dec = 2500+5000=7500

      Jan = 2500+7500 = 10,000 so on..

       

      How do I calculate this ?

       

      Thank you!

        • 1. Re: Displaying table kinda cross tab and calculation help
          Shinichiro Murakami

          Hi Dipti

           

          I'm not quite sure I understand your request, anyways

           

          [Month Header]

          if [Year]="2017" then "" else [Month] end

           

          [FSIS Allocation sraight]

          if attr([Year])="2016"

          then min({fixed :sum(if [Year]="2017" then [FSIS Allocation] end)}/12)

          else sum([FSIS Allocation]) end

           

           

           

           

          Thanks,

          Shin

          • 2. Re: Displaying table kinda cross tab and calculation help
            dipti.patel.2

            Thank you for your help! But this is not exactly what i am looking for -

             

            Ok So what i want is to show Month Oct to Sep even if there is no data as columns

            Then Show Total Execution for current year as row 1

            Then Show Total Execution for prior year as row 2

            And show the Running Sum Allocation as u have shown only for current year as row 3

             

            Also I will have Year as a filter so if 2017 is chosen then it is current year and prior is 2016. So I want table to  dynamic based on the year selected.

            Then I want to show a line graph for the above 3 rows (measures) against the months Oct to Sep ( fiscal year )

             

            Really need some help as this is driving me nuts as the allocation data is not available monthly so i am getting Null as a month and it is making harder to work.

             

            Let me know if this is a little clear.

             

            thank you!

            • 3. Re: Displaying table kinda cross tab and calculation help
              dipti.patel.2

              Please take a look at the attached workbook.

               

              How do i calculate the running total for the FSIS Straight Allocation row for the current year 2017?

               

              Also is it possible to show FSIS Straight Allocation row only for current year ie 2017 and not for any prior year period.

              And would it be possible to show the layout as -

               

                                                                                    Oct   Nov  Dec  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep

              Current Year Total Execution

              Prior Total Execution

              Current Year FSIS Straight Allocation

               

              Then show a line graph for the above table as well.