# 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 Tuesday, September 24, 2013 9:08 AM
Tuesday, September 24, 2013 9:07 AM