Dividing 2 calculated fields?

Question & Answer
, , ,
1

Hi. I have 2 calculated fields that I derive it myself. I want to divide one by another. When I’m creating a new calculated field (FNAB) with this syntax, it shows nothing in the pivot table

{Food & Non-Alcoholic Expenditure} / {Private Consumption Expenditure} * 100

image
image686×777 102 KB

Could anyone help with this problem ?

2

Hi @junyangggggg,
I’m curious to see what the 2 calculated fields are that you’re using in this, could you share? It may work best if you use Arena to create a sample dashboard so that we can better assist.

Thank you!

3

Hi duncan, the 2 calculated fields that I am using is defined as

ifelse({DataSeries} = "Private Consumption Expenditure", {Value}, NULL)
ifelse({DataSeries} = "Food & Non-Alcoholic Expenditure", {Value}, NULL)
4

Hi @junyangggggg,

Can you try sumOver with partition of TimePeriod for the nested calculated fields?

I presume, since the aggregation is not done at TimePeriod level, the calculation is getting executed at record level and for each record either of the nested calculated field is returning NULL, resulting in NULL for the FNAB field.

Thanks,
Prantika

1 Like
5

Hi @prantika_sinha, thanks for replying! I’ve tried

sumOver(sum({Food & Non-Alcoholic Beverages})/sum({Private Consumption Expenditure}), [TimePeriod])

but the values are still NULL for FNAB

6

Sorry, I believe my previous response was a bit confusing. Let me break this down into steps.

Step 1: Food & Non-Alcoholic Beverages = sumOver(sum(ifelse({DataSeries} = “Food & Non-Alcoholic Expenditure”, {Value}, NULL)),[TimePeriod])

Step 2: Private Consumption Expenditure = sumOver(sum(ifelse({DataSeries} = “Private Consumption Expenditure”, {Value}, NULL)), [TimePeriod])

Step 3: FNAB = {Food & Non-Alcoholic Expenditure} / {Private Consumption Expenditure} * 100

7

thanks @prantika_sinha. It works!!