Calculation

[Feras95p]

Dear all,

As you can see in the picture below I have calculated in Jan, Feb, March and Aprill the percentage from the 2019 and 2020 for example Jan=((Jan-20 - Jan-19)/Jan19)*100  but I have problem when I'm having 0 values in 2019 like in April it's giving me null. I want to have it like it's increased 100% is that possible?

[echuong1]

I'd probably configure each to be a conditional statement and if the value is 0 to make it 100.

Ex:

IF isnull(Jan-20) or isnull(Jan-19) then 100

else ((Jan-20 - Jan-19)/Jan-19)*100

endif 

[ImadZidan]

Hello @Feras95p ,

I am not seeing a reason to give you null. mathematically speaking

April = ((4477742-0)/4477742) * 100 = 100

I suspect that you have datatype issue in the formula. Please revisit columns and check datatypes and ensure they are numeric.

If you are still facing the issue. share the data, we will provide a workflow.

I hope this helps.

[Feras95p]

I'm subtracting the data of 2020 from 2019 and divided by the data of 2019 so April = ((0-4477742)/0) * 100

[Feras95p]

Ok thanks but I have one more question, If I want to have the percentage % how can I put it in alteryx? because I'm multiplying by 100 and if I want to have % it gives me error  

[echuong1]

Adding the "%" makes it a string, and not a number. You can add this if you convert it to a string and concatenate the percent symbol.

Ex: tostring([value or formula here])+"%"

datatype would need to be a string format

[Feras95p]

Great ! and if i want to take only two digit after the comma how can I do that?

[ImadZidan · Accepted solution]

hello @Feras95p ,

use toString, it allows you to specify the decimals.see in bold below

ToString((([n2]-[n1])/[n2])*100,2) + '%'