locked
How to create Year to Date KPI in Dashboard RRS feed

  • Question

  • Hi Experts!

         We are tryig to do the Year to date KPI.
              i have created a year filter and the quarter filter in the Dashboard, if i select a year and a quarter it should give the aggregate value of the starting  quarter upto the current quarter for the selected year.

       e.x:

                  if am selecting Year "2002" and Quarter "Q3"
                      it should give the aggregated value of "Q1-Q3" of the year "2002".
              In the Actual value it should show the Aggregate value of "Q1-Q3" of  year "2002".
                 In the Target value it should show the aggregat evalue of "Q1-Q3" of the year "2001".

                 How  can i create this KPI in the dashboard?
        
    Monday, March 3, 2008 3:14 PM

Answers

  • Siva,

     

    The behaviour you're experiencing is a common one and lies with ParallelPeriod coupled with the data available in AdventureWorksDW.

     

    Have a look at this post from Mosha Pasumansky, one of the creators and gatekeepers of MDX that should help explain the behsaiour. Below is an extract from that post that will get you started.

     

    "ParallelPeriod won't work correctly in Calendar hierarchy ! Remember, that dates start from July 1, 2001 in the [Ship Date] dimension. ParallelPeriod really knows nothing about it, the way it works is exactly the way the Cousin function works, i.e. it finds the member of the hierarchy which is at the same relative position as the current one. Therefore, ParallelPeriod for January 2002 instead of going to January 2001 (which doesn't exist) goes to July 2001, February 2002 goes to August 2001 and so on ! Of course, in 2003 everything normalizes, since 2002 has all months, but the results for the first half of 2002 for ParallelPeriod are clearly wrong."


    Cheers,

    Nick

    Tuesday, March 4, 2008 8:33 PM

All replies

  • Hi Siva,

     

    I would first look to reconfiguring your filters. Try combining both the Year and Quarters into one. this way you be assured that when you select a particular quarter the cube knows which year the quarter is located. All of the time hierarchy logic will need to be taken care of in the source cube - look carefully to ensure your attribute relationships have been defined appropriately.

     

    You should also check how the year-to-date is being calculated within the KPI and ensure this is correct.

     

    Cheers,

    Nick

     

    Monday, March 3, 2008 6:49 PM

  • Hi Nick!

             Thanx for u'r valuable information.
         still we are facing some problem with the cube.

        we are using the "Adventure worksDw" cube, we have created a filter using "Date.calendar" Hierarchie as you mentioned.
         We used the following code in our KPI,

               Actual : Aggregate(YTD([Date].[Calendar].CurrentMember), [Measures].[Sales Amount])

              Taget : Sum(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember)), [Measures].[Sales Amount])


            This two codes are working perfect with all the years that as all the 4 Quarters.
           In our case in the year "2001", we are having only two Quarters "Q3&Q4".

         If i select the Quarter "Q3" of the Year "2002" its not showing any value in the Target.
         but if i select the Quarter "Q1" of the year "2002" its showing the Quarter "Q3" value of  the year"2001".
       But Actually if i select "Q1 (or) Q2" of "2002", in the target it should not show any value. Becoz the year 2001 does not  have the values for  "Q1&Q2". if i select "Q3"  of "2002" it should show the sum of "Q3" value of the year "2001" in the target.
     Similarly if select "Q4" of "2002" it should show the sum of "Q3 & Q4" of  "2001" in the Target value.
      

        Kindly Help Us to solve this Problem.

      Thanx,
    Tuesday, March 4, 2008 2:39 PM
  • Siva,

     

    The behaviour you're experiencing is a common one and lies with ParallelPeriod coupled with the data available in AdventureWorksDW.

     

    Have a look at this post from Mosha Pasumansky, one of the creators and gatekeepers of MDX that should help explain the behsaiour. Below is an extract from that post that will get you started.

     

    "ParallelPeriod won't work correctly in Calendar hierarchy ! Remember, that dates start from July 1, 2001 in the [Ship Date] dimension. ParallelPeriod really knows nothing about it, the way it works is exactly the way the Cousin function works, i.e. it finds the member of the hierarchy which is at the same relative position as the current one. Therefore, ParallelPeriod for January 2002 instead of going to January 2001 (which doesn't exist) goes to July 2001, February 2002 goes to August 2001 and so on ! Of course, in 2003 everything normalizes, since 2002 has all months, but the results for the first half of 2002 for ParallelPeriod are clearly wrong."


    Cheers,

    Nick

    Tuesday, March 4, 2008 8:33 PM