4 Replies Latest reply on Aug 23, 2016 5:10 AM by Muhammad Khan

    How to show data for prevous year's same term

    Muhammad Khan

      Hello Forum

       

      Is there any way to create a calculated filed which shows data for same term in previous year

       

      I have created following two measures form number of record, there data are the same

       

      1) Total Marks for Winter 2015

      2) Total Marks for Winter 2016

       

      I also have following dimensions

       

      1) Courses

      2) Terms

       

      I want to show them in following way

       

                                              Courses 1    Courses 2   Courses 3   Courses 4   Courses 5

      Total Marks for winter  2015          20                   30             10                 6                    7

      Total Marks for Winter 2016          10                    11              12               40                   19   

       

      Thanks

       

      Muhammad

        • 1. Re: How to show data for prevous year's same term
          Benjamin Greene

          I can think of a couple ways to do this, but it's hard to say which one would be the best/fastest for you without looking at a packaged workbook. Assuming you already have a Term field that has values like "Winter 2015," my guess would be to first create two calculated fields: Term Name and Term Year.

           

          Term Name:

          LEFT([Term], FIND([Term], " ")-1)

          Term Year:

          RIGHT([Term], 4)

           

          Then I would right click Term and select Create>Parameter and click OK, then show the parameter control. Finally, I would create a Filter calculated field that says:

           

          IF [Term Name]=LEFT([Term Parameter], FIND([Term Parameter], " ")-1)

          AND (INT([Term Year])=INT(RIGHT([Term Parameter], 4)) OR INT([Term Year])=INT(RIGHT([Term Parameter], 4))-1)

          THEN "YES"
          ELSE "NO"
          END

           

          Finally, I would filter on only the "YES" values in the Filter calculated field.

          1 of 1 people found this helpful
          • 2. Re: How to show data for prevous year's same term
            Muhammad Khan

            Hello Benjamin

             

            Thank you so much for your response, I tried your suggested formula but it seems that it is not solving the issue, I have attache sample workbook for your review. If you need any clarification kindly let me know.

             

            Thanks

             

            Muhammad

            • 3. Re: How to show data for prevous year's same term
              P N

              Hi Muhammad:

               

              Please find the attached solution and look for the 3 calculated fields: 1) Term Name 2) Term Year 3) Comp.

              It borrows from what Benjamin had alluded to in his reply. Since he had replied before you posted the workbook I have done some modifications to reflect the actual data.

               

              Thanks

              1 of 1 people found this helpful
              • 4. Re: How to show data for prevous year's same term
                Muhammad Khan

                Hello P N

                 

                Thank you so much for solution. It really solved my issue. Thank you

                 

                Muhammad