Percentage column in a calc view

[Former Member]

Hi All,

Can any one suggest how to do the percentage calculation(calculated attribute) on two aggregated columns in a graphical calc view?

Goutham

[Former Member]

Create a new calculated column COLUMN_C with formula COLUMN_A / COLUMN_B * 100.0

Please share more details for a more detailed answer.

[Former Member]

I did that but the percentage is getting calculated at each row level where as i need the percentage on aggregated column's i/e (SUM(COLUMN_A)/SUM(COLUMN_B))*100

[Former Member]

Provide simple sample data, simple model example, expected results, and actual (incorrect results) and I'd be happy to have a look.

Shouldn't be much problem getting the calculation to occur after aggregation.

[Former Member]

Below is the senario:

I need to show the number of customers with N/A as customer name, total number of customers and the percentage of customers with N/A as customer name from customer master table in the output of the calc view.

For modeling the above, I have created a graphical calc view with the customer table in projection and calculated columns TOTAL_CUSTOMERS and CUSTOMERS_NA_NAME as shown below and at the aggregation level, i have created calculated column PERCENTAGE_NA NAME as shown below:

CALCULATED COLUMN                          FORMULA

--------------------------------------                       -----------------------------------

TOTAL_CUSTOMERS                             1

CUSTOMERS_NA_NAME                 IF("CUSTOMER_NAME" = 'N/A ','1',0)

PERCENTAGE_NA NAME                ("TOTAL_CUSTOMERS "/"CUSTOMERS_NA_NAME") * 100

Below are the results:

Actual result:

TOTAL_CUSTOMERS                       -- 46,464

CUSTOMERS_NA_NAME                 -- 547

PERCENTAGE_NA NAME                -- 0

Expected result:

TOTAL_CUSTOMERS                       -- 46,464

CUSTOMERS_NA_NAME                 -- 547

PERCENTAGE_NA NAME                -- 1.18

Goutham

[Former Member]

Looks like you have the numerator and denominator mixed up here, assuming that was just a mistake in your write-up (but might want to check your model): PERCENTAGE_NA NAME                ("TOTAL_CUSTOMERS "/"CUSTOMERS_NA_NAME") * 100

Assuming your model's calculated columns has the correct formula, make sure all respective data types are DECIMAL and not INTEGER. It is likely calculating division with 0s and thus truncating the result - therefore you get 0.

(I haven't test the ins and outs of implicit casting if one of the fields is decimal and the other integer - might work as well - but to be safe, stick with all DECIMAL types)

[Former Member]

Sry that was a typo  in fact i used    ("TOTAL_CUSTOMERS "/"CUSTOMERS_NA_NAME") * 100 and all the data types are decimals still its 0 for percentage.

[Prabhith]

Hi ,

Please try using the following option while working on the calculated Measure.

BR

Prabhith

[Former Member]

Hi Prabhith, that option is only accessible for calculated columns in Analytic Views. According to Goutham, he's working on a Calculation View.

[Former Member]

Please test on a very small sample set - i.e. 3 rows, in a very simple calculation view. If that works - then examine the differences between that calculation and the more complex, original model.

If it still doesn't work, copy the small dataset here as well as screenshots of the CalcView so that I can duplicate.

Thanks