Percentage formula

[psinghania]

Hi,

I need to divide variance for Current year vs previous year sales with previous year sales and the output should in % form. Based on this % I require to calculate another formula as variance between CY & PY GM multiplied by the above output.

I tried using the formula below but the output is text and hence the second formula results in error:

First formula: Tostring([Sales Variance MTD]/[Net_Sales_Aug_2018])*100,2)+'%'.

If I simply remove the string it gives pure number which is not useful for calculating the second formula.

Second formula: [GM Variance MTD]*(1+[Sales Variance MTD in %])

I am attaching the excel working file with actual formulas for reference

Is there a way to calculate this with the help of alteryx?

[ChrisHe · Accepted solution]

Hey @psinghania 

I've recreated the formulas in your Excel sheet in AYX using the sample data you sent me - all of the calcs are just labeled with their names plus _AYX to help you differentiate.

The main thing you're missing is that you shouldn't be converting your numbers to strings with percent signs until the very end of your calculations.  You need them to stay Doubles (no rounding of decimal places) until after you've done all of the math.  You can see in my calculations that I was able to imitate your Excel functions and get the same results.  Then, at the end of all calculations you can choose to change things back to a string with a percent sign if necessary (I recommend leaving as standard numbers).

I've also added some reporting tools at the end to show how you can use those to export to a formatted Excel sheet with percent signs. Let me know how else I can help and if this has helped you make sure to mark as a solution so others can make use as well!

-Chris

[Dazzerman]

One way to achieve this is to have the calculated field as a Float variable, and a separate variable for the display format.  Then you shouldn't get any errors.

[psinghania]

Thanks Chris! It works!

[JokeFun]

brilliant way to show % as values.