登录
 
主页技术资源库学习下载支持社区论坛
IT 专业人士的资源 >
> 
> 
How to work For based on time Period as filter in PPS

Answered How to work For based on time Period as filter in PPS

  • 2012年3月13日 7:53
     
     
    登录进行投票
    0

    Hi ,

    I need to create period filter that means based on the selected value from the filter (i.e MTD,QTD,YTD)

    I have a dashboard in each report the period filter as to effect, if i select one value then only that value related mesure as to shown in the report.

    i have one time dimension with two hierarchies one is Calendar and another is fisical. in my dasboard i have two filters one is year and another one is period. i can able to get the filtering on reports for year report.

    but can any one help on the how to create and use the period filter and which filter is perfect for the above scenario.    

     

全部回复

  • 2012年3月26日 7:45
    版主
     
     已答复
    登录进行投票
    0
    Hi Lokesh,

    You can create a Time Intelligence filter by using Dashboard Designer. Use the Time Intelligence filter template to create a dashboard filter that contains a list of rolling time periods that you specify. Some examples of time periods that you might use include Last Two Years, Last Six Months, and Last 30 Days,See To create a Time Intelligence filter

    Thanks,
    Lhan Han
     
  • 2012年4月4日 20:37
     
     
    登录进行投票
    0

    This is how I do MTD, QTD, and YTD, along with 1yr ago through 5yrs ago . . . you get the idea pretty quick:

    DECLARE
    @TheDate  DATETIME
    , @MTDStart DATETIME
    , @MTDEnd DATETIME
    , @MTDEND2 DATETIME
    , @QTDStart DATETIME
    , @QTDEnd DATETIME
    , @YTDStart DATETIME
    , @YTDEnd DATETIME
    ,@YTD1YRAGOS DATETIME
    ,@YTD1YRAGOE DATETIME
    ,@YTD2YRAGOS DATETIME
    ,@YTD2YRAGOE DATETIME
    ,@YTD3YRAGOS DATETIME
    ,@YTD3YRAGOE DATETIME
    ,@YTD4YRAGOS DATETIME
    ,@YTD4YRAGOE DATETIME
    ,@YTD5YRAGOS DATETIME
    ,@YTD5YRAGOE DATETIME


    SET @TheDate = '1/1/2005'
    SET @MTDStart = DATEADD(MONTH, DATEDIFF(MONTH, 0, @TheDate), 0)
    SET @MTDEnd = DATEADD(MONTH, 1, DATEADD(DAY, -1, @MTDStart))
    select @MTDStart As MTDStart, @MTDEnd As MTDEnd

    SET @QTDStart = DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @TheDate), 0)
    SET @QTDEnd = DATEADD(QUARTER, 1, DATEADD(Day, -1, @QTDStart))
    Select @QTDStart As QTDStart, @QTDEnd As QTDEnd

    SET @YTDStart = DATEADD(YEAR, DATEDIFF(YEAR, 0, @TheDate), 0)
    SET @YTDEnd = DATEADD(DAY, -1, DATEADD(Year, 1, @YTDStart))
    Select @YTDStart As YTDStart, @YTDEnd As YTDEnd

    --LAST YEAR
    SET @YTD1YRAGOS = Dateadd(YEAR, Datediff(YEAR, 0, Dateadd(YEAR, -1, @TheDate)), 0)
    SET @YTD1YRAGOE = Dateadd(mm, 0, DATEADD(yy, DATEDIFF(yy, -1, @YTD1YRAGOS), -1))
    SELECT @YTD1YRAGOS AS YTD1YRAGOS, @YTD1YRAGOE AS YTD1YRAGOE

    --Two Years Ago
    SET @YTD2YRAGOS = Dateadd(YEAR, Datediff(YEAR, 0, Dateadd(YEAR, -2, @TheDate)), 0)
    SET @YTD2YRAGOE = dateadd(ms, 0, DATEADD(yy, DATEDIFF(yy, -2, @YTD2YRAGOS), -1))
    SELECT @YTD2YRAGOS AS YTD2YRAGOS, @YTD2YRAGOE AS YTD2YRAGOE

    --Three Years Ago
    SET @YTD3YRAGOS = Dateadd(YEAR, Datediff(YEAR, 0, Dateadd(YEAR, -3, @TheDate)), 0)
    SET @YTD3YRAGOE = dateadd(ms, 0, DATEADD(yy, DATEDIFF(yy, -3, @YTD3YRAGOS), -1))
    SELECT @YTD3YRAGOS AS YTD3YRAGOS, @YTD3YRAGOE AS YTD3YRAGOE

    --Four Years Ago
    SET @YTD4YRAGOS = Dateadd(YEAR, Datediff(YEAR, 0, Dateadd(YEAR, -4, @TheDate)), 0)
    SET @YTD4YRAGOE = dateadd(ms, 0, DATEADD(yy, DATEDIFF(yy, -4, @YTD4YRAGOS), -1))
    SELECT @YTD4YRAGOS AS YTD4YRAGOS, @YTD4YRAGOE AS YTD4YRAGOE

    --Five Years Ago
    SET @YTD5YRAGOS = Dateadd(YEAR, Datediff(YEAR, 0, Dateadd(YEAR, -5, @TheDate)), 0)
    SET @YTD5YRAGOE = dateadd(ms, 0, DATEADD(yy, DATEDIFF(yy, -5, @YTD5YRAGOS), -1))
    SELECT @YTD5YRAGOS AS YTD5YRAGOS, @YTD5YRAGOE AS YTD5YRAGOE

    Ryan Shuell