Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Problem in KPI parallel period

Answered Problem in KPI parallel period

  • Saturday, January 19, 2013 9:45 PM
     
      Has Code
    Sign In to Vote
    0
    Hi everyone, can you kindly check this code. It returns a null value. I'm just trying to learn SSAS but KPI it's going to make me crazy. Thank you.  
    Case
 
  When (([Measures].[Sales Amount]-[Measures].[Total Product Cost])/[Measures].[Sales Amount])*100 >
    (ParallelPeriod([DUE DATE].[Calendar Year],
      1, [DUE DATE].[Calendar Year]), (([Measures].[Sales Amount]-[Measures].[Total Product Cost])/[Measures].[Sales Amount])*100)
    Then 1
  When (([Measures].[Sales Amount]-[Measures].[Total Product Cost])/[Measures].[Sales Amount])*100 =
    (ParallelPeriod([DUE DATE].[Calendar Year],
      1, [DUE DATE].[Calendar Year]), (([Measures].[Sales Amount]-[Measures].[Total Product Cost])/[Measures].[Sales Amount])*100)
    Then 0

     

All Replies

  • Sunday, January 20, 2013 8:21 AM
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    Hi DIEGOCTN ,

    The ParallelPeriod need a level for the first argument ... Please try the next code :)

    with 
member b as
( (([Measures].[Sales Amount]-[Measures].[Total Product Cost])/[Measures].[Sales Amount])*100)
member a as
Case
 
  When (([Measures].[Sales Amount]-[Measures].[Total Product Cost])/[Measures].[Sales Amount])*100 >
    (ParallelPeriod([DUE DATE].[Calendar Year].[Year],
      1, [DUE DATE].[Calendar Year]), b)
    Then 1
  When (([Measures].[Sales Amount]-[Measures].[Total Product Cost])/[Measures].[Sales Amount])*100 =
    (ParallelPeriod([DUE DATE].[Calendar Year].[Year],
      1, [DUE DATE].[Calendar Year]), b)
    Then 0
end
select a on 0
,[DUE DATE].[Calendar Year].[Year].members on 1
from [Cube]

    Regards, David .

     
  • Sunday, January 20, 2013 9:08 AM
     
     
    Sign In to Vote
    0
    Error 7 Is expected an MDX, but it has a complete instruction...
     
  • Sunday, January 20, 2013 10:21 AM
     
     
    Sign In to Vote
    0

    Sorry.... I can't understand you .

    What is the exact error you get ?

    I was checking the exact code with my measures/cube/dims and it worked just fine :)

    Regards, David .

     
  • Sunday, January 20, 2013 11:02 AM
     
     Answered Has Code
    Sign In to Vote
    0
    I resolved with:  
    Case
 
  When (([Measures].[Sales Amount]-[Measures].[Total Product Cost])/[Measures].[Sales Amount])*100 >
    (ParallelPeriod([DUE DATE].[Calendar Year].[Year],
      1, [DUE DATE].[Calendar Year]))
    Then 1
  When (([Measures].[Sales Amount]-[Measures].[Total Product Cost])/[Measures].[Sales Amount])*100 =
    (ParallelPeriod([DUE DATE].[Calendar Year].[Year],
      1, [DUE DATE].[Calendar Year]))
    Then 0
end
    I dunno why your code returns an error...I'm starting to learn the parallelperiod. I tried to translate the error, it's like if your expression was too complicated for a KPI
     
  • Sunday, January 20, 2013 1:36 PM
     
     
    Sign In to Vote
    0

    Happy you've found the answer :))

     Please mark as answered .

    Regards, David .