Report Crashes when calculating percentage variance of two columns.

Mat Chabros shared this problem 2 years ago
Resolved

Good morning Yellowfin team,

The problem I have is that my report crashes and submits an error message indicating that something is wrong with the SQL. The problem occurs only when I try to calculate a % variance of two columns (percentage change between two columns to be more precise).


I am using calculated fields in order to achieve this, the formula I am using is as follows:

  • (Col 2 - Col 1)/Col 1

The odd thing is however, it works just fine with certain data while with others it does not (my previously created Reports work just fine for instance, however, not when I copy it and filter to a different region).

I also concluded that it is not a matter of the View. Any idea how this could be fixed?

The last thing I'd like to mention, this happens only with Calculated Fields, if I do an advanced function and calculate the % difference of two columns, the report works just fine.

I would be grateful for any advice on how to proceed with that problem.

This is the error I receive:

9842034452eafc33ca7e1e8f4468d564

Best Answer
photo

Hi Mat,

My guess here is that you are encountering a divide by 0 error. I would suggest instead creating your calculated field with the following:

CASE WHEN Col 1 = 0 THEN 0 ELSE (Col2-Col1)/Col1

Let me know if this doesn't work for you!

Regards,

Nathan

Comments (2)

photo
1

Hi Mat,

My guess here is that you are encountering a divide by 0 error. I would suggest instead creating your calculated field with the following:

CASE WHEN Col 1 = 0 THEN 0 ELSE (Col2-Col1)/Col1

Let me know if this doesn't work for you!

Regards,

Nathan

photo
1

Good morning Nathan,

Thanks for taking the time to address my problem. It worked, however, whenever the column with the % variance was not included, Col 1 and Col 2 were populated with numbers, thus, I am a little bit confused how come your solution worked.


Best wishes,

Mat

photo
1

So upon further investigations, that was the problem, some regions had 0 in revenue, thus it was showing that problem.

Once again, thank you very much Nathan!

photo
1

Hi Mat,

Glad to hear it and no problem!

Regards,

Nathan

photo