13 Replies Latest reply on Jul 12, 2017 11:11 AM by Hari Ankem

Calculated column as a difference of table calculations

I need to add third column that calculates a difference between pct of Male and Female by category.

These percentages are quick table calculations. Additionaly I would need to condition format that new columns so that I emphasize if the difference is greater than 5 pct or less than -5 pct.

• 1. Re: Calculated column as a difference of table calculations

Thank you for this answer Mark. I tried to move the calculation into measures. This is what I got

Calculation1 = SUM([Number of Records]) / TOTAL(SUM([Number of Records])).

How do I create this new column based on the difference between Calculation for Male and Female ?

• 2. Re: Calculated column as a difference of table calculations

I am still not sure how to do this.

Attached is the example similar to the dataset I've got.

• 3. Re: Calculated column as a difference of table calculations

I can't beleive no one has a solution for this issue. I know in Qlikview this is as easy as adding Col3 that is equal to Col1 - Col2

• 4. Re: Calculated column as a difference of table calculations

Sorry, been going through a job transition. I'll take a look tonight

• 5. Re: Calculated column as a difference of table calculations

I'm struggling... the Table calc is what's throwing me off... I hate to do this, but Rody Zakovich do you have any ideas?

-Mark

• 6. Re: Calculated column as a difference of table calculations

Here you go:

I have changed the formula for Difference and placed the Male Count, Female Count and Difference to the measures, and did a table calculation to display the percentages.

1 of 1 people found this helpful
• 7. Re: Calculated column as a difference of table calculations

It's not that noone has a solution for this. It's just that noone saw it. Usually, if a question has multiple responses, people do not see it because it is assumed that one has already been helped.

1 of 1 people found this helpful
• 8. Re: Calculated column as a difference of table calculations

Good solution from Hari Ankem .

Assuming your actual data is structured like this sample data and you want to calculate percentage per Category per rating, then try the formulas below for correct percentage and difference calculation.

[Male %]

SUM(IF [Gender1]="Male" THEN [Number of Records] END)/SUM([Number of Records])

[Female %]

SUM(IF [Gender1]="Female" THEN [Number of Records] END)/SUM([Number of Records])

[Difference]

[Male %] - [Female %]

Hope this helps.

Ossai

• 9. Re: Calculated column as a difference of table calculations

Thank you Hari Ankem

It appears that in this solution - difference column is not giving me the correct difference between male and female pct within a category/rating.

• 10. Re: Calculated column as a difference of table calculations

Osai,

In this solution - you got really close but male and female pct need to go across ratings within a category (as in the example I attached).

• 11. Re: Calculated column as a difference of table calculations

Can you please tell me what is your expected result here? I have included the counts and the differences and their percents as computed in the above solution.

• 12. Re: Calculated column as a difference of table calculations

So what I need is a pct difference...say rating E and Category 1: Male is 22% Female is 47%  - I need difference to be -25%. For rating E Category 3: Male is 22%, Female is 11%. Difference should be 11%

• 13. Re: Calculated column as a difference of table calculations

OK. Here you go.