none
How to add - 1 to existing query ... answer asap

    Question

  • Hi All, Below metric is used in one of our cubes and business requirement is to subtract 1 from current result set this metric is giving for eg: 98% is current result set and business going fwd want to see it as -2%

    [SNP COST AS SLD USD Prior Year Chg %] = Iif ((ParallelPeriod([fiscal calendar].[fiscal period].[fiscal year],1,[fiscal calendar].[fiscal period]),[Measures].[SNP COST AS SLD USD]) = 0, Null, ([fiscal calendar].[fiscal period],[Measures].[SNP COST AS SLD USD]) / (ParallelPeriod([fiscal calendar].[fiscal period].[fiscal year],1,[fiscal calendar].[fiscal period]),[Measures].[SNP COST AS SLD USD]))

    =>>>> What is the best way of adding "minus 1" to above formula. If i just add -1 at end with respective brackets it is working only if this metric returns value against specific attribute but if there is no data available for a particular attribute it does not display blank instead it is displaying as -100%. Kindly suggest right way of adding -1 to this formula.

    Friday, September 20, 2013 11:06 PM

All replies

  • you can create another calculated measure based on [SNP COST AS SLD USD Prior Year Chg %], like:

    [The new Measure] = IIF(([SNP COST AS SLD USD Prior Year Chg %]-1)!=-1,([SNP COST AS SLD USD Prior Year Chg %]-1),Null)

    Saturday, September 21, 2013 9:31 AM
  • Is there any way to put the logic in the same metric>> business wanted to see the new result for the same metric. do not want a new metric to be created
    Monday, September 23, 2013 8:19 PM
  • If you want it to display -100% for blanks, change the ", Null," to ", -1, ".

    Monday, September 23, 2013 9:02 PM
  • oops I ACTUALLY DO NOT WANT -100% to be displayed for blank . i want it to be blank as it is now and ensure -1 works only if there is an value for that metric. if the current formula of that metric returns null value i still want it to be blank even after adding -1
    • Edited by grangar Tuesday, September 24, 2013 9:08 AM
    Tuesday, September 24, 2013 9:07 AM