5 Replies Latest reply on Mar 1, 2017 8:13 AM by Joe Oppelt

    Cumulative calc based on the month selected

    dipti.patel.2

      I have a report with the few measures and then there are calculated fields based on those measures in the worksheet.

      I have a year,month as a filter and its for fiscal year starting from Oct to  Sep. So for fiscal year 2017 it starts from Oct 2016 to Sep 2017.

       

      I have the month as a single select so when a user selects Oct the measures and other calculated fields show Oct related values.

      When user selects Nov I want the measures and the calculated fields to show cumulative amount ie. adding the Oct + Nov month values.

      So on for Dec = Oct  + Nov + Dec

                      Jan = Oct  + Nov + Dec + Jan

      untill Sep where its culumative from Oct 2016 to Sep 2017.

       

       

      Sorry I have not done cumulative calc based on filters so asking it out here for help.

       

      thank you!

        • 1. Re: Cumulative calc based on the month selected
          Mia Lee

          Hi, Dipti.

           

          Your data has 2012 value only - means that instead of calc 2016 Oct,Nov,Dec and 2017 Jan data, it'll calc 2012 Jan,Oct,Nov,Dec - which I don't think what you want.

           

          Until you fix the data, what I can suggest is to

               - create 2 parameters: Startdateand Enddate

               - create a date out of year and month: DATE([Month]+'-1'+'-'+[Year])

               - create a calc field using the date and 2 parameters: If startdate>=date and enddate<date then measure else 0 end

           

          Hope this helped. Please let me know if you have further questions.

          Thanks,

           

          Mia

          • 2. Re: Cumulative calc based on the month selected
            Joe Oppelt

            In the attached I made a basic sheet showing one of your measures.  See Sheet 2.  It contains a new calc that is a running sum of your measure.

             

            Now look at Sheet 3.  On this sheet I made a table calc that I use as a filter.  When you use a table calc as a filter, the whole underlying table remains intact, and the filter determines what to display from that table.  (A quick filter actually eliminates rows from the table.)  By keeping the table intact, the running sum numbers also remain intact.  Now when you display any month, the running value gets displayed.  You will need to do this for all your measures.  Individual calcs for each.

             

            Sheet 4 is another method of using a table calc.  this one uses a parameter to determine what month the user wants to see.  So in this case the output from the table calc filter is either a 1 or a 0, and I selected for value = 1.

            • 3. Re: Cumulative calc based on the month selected
              dipti.patel.2

              Hi Joe Oppelt,

               

              Thank you for response, it helps me in understanding how i can move forward.

               

              But one concern is making use of parameters. If I use parameters then I have to show Oct to Sep fiscal months, but say for 2017 if I have only till Feb month data I want to show only Oct to Feb as months. Wouldn't this be possible without using Parameters?

               

              Meanwhile I will atleast give a try doing like you have in sheet 4 for my remaining measures.

              • 4. Re: Cumulative calc based on the month selected
                dipti.patel.2

                Mia,

                 

                The data is for 2012 and I have the months for it from Oct to Sep in the data.

                So currently you can ignore my example of using 2016 and I just want to have the measures cumulative for months.

                Like if we select Nov then all measures shld show Oct + Nov data measures.

                • 5. Re: Cumulative calc based on the month selected
                  Joe Oppelt

                  If you look at the way I did it in Sheet 3, I think that will only show the actual values possible in your data.

                  I can test it out for you if you if you rebuild your packaged workbook with your current 2012 data, but add in two months of 2013 data.