In Pivote table if row wise values are less then column total then Red else Green how to achieve

[P_Kale]

Hi,

I have created a pivote table and want to compare row wise total against column total. If the row wise values are less then column total then i want to give Red colour else Green to the value.

below given formula I have used to calculate %.

if(num(Count({< EARLY_NON ={'EARLY'}>} POLICY_NO) / Count(total <PI_PO_INCOME_BUCKETING, FY_YEAR> POLICY_NO),'##.0%'

but under Text colour expression i am writing something like this  but what to right after initial expression don't know. 

if(num(Count({< EARLY_NON ={'EARLY'}>} POLICY_NO) / Count(total <PI_PO_INCOME_BUCKETING, FY_YEAR> POLICY_NO),'##.0%') <=

Pl help to resolve the issue.

Thanks in advance.

@marcus_sommer 

@Anil_Babu_Samineni 

@swuehl 

@sunny_talwar 

@kaushiknsolanki 

[qv_testing]

@P_Kale , If you provide some sample data and output - that would be great and you will get quick response.

[kaushiknsolanki]

Your Row total will never be higher than your column total.

Please provide example with sample data and expected output from that sample data.

[P_Kale]

Hi Thanks @kaushiknsolanki 

I have used below given formulas to calculate %

1) % NOP = num(Count({< SALES_NONSALES ={'Sales'}>} POLICY_NO) / Count(total <INCOME_BUCKETING, FY_YEAR> POLICY_NO),'##.0%')

2) % Payout in SALES_AMT = num(Sum({< SALES_NONSALES ={'Sales'}>} SETTLED_AMOUNT) / sum(total <INCOME_BUCKETING,FY_YEAR> SALES_AMT),'##.0%')

below given out-put i am looking for where if values are less than total then should be in red.

Thanks in advance

[P_Kale]

Thanks @qv_testing 

I have used below given formulas to calculate %

1) % NOP = num(Count({< SALES_NONSALES ={'Sales'}>} POLICY_NO) / Count(total <INCOME_BUCKETING, FY_YEAR> POLICY_NO),'##.0%')

2) % Payout in SALES_AMT = num(Sum({< SALES_NONSALES ={'Sales'}>} SETTLED_AMOUNT) / sum(total <INCOME_BUCKETING,FY_YEAR> SALES_AMT),'##.0%')

below given out-put i am looking for where if values are less than total then should be in red.

[Anil_Babu_Samineni]

@P_Kale As always, To avoid time consumption from us, We will appreciate to create a sample QVF along with your excel file and from there what you expect can be shared, so that we can have a look the logics built inside. 

[P_Kale]

Thanks @kaushiknsolanki 

Below attach is the sample qvf and excel sheet for out-put required.

As per excel sheet the row values % which are less than total % should be in red color.

Thanks in advance.

[kaushiknsolanki]

Here is your solution.

[P_Kale]

Thanks @kaushiknsolanki for your time.

Actually i want to compare the Total of columns against the row wise values.

In attached excel sheet i have given the expected out-put.

EXPECTED_OUTPUT
FY_YEAR	Totals			2015-16			2016-17			2017-18
Income_Bucketing	Sum(EARLY)	Sum(NOP)	NOP%	Sum(EARLY)	Sum(NOP)	NOP%	Sum(EARLY)	Sum(NOP)	NOP%	Sum(EARLY)	Sum(NOP)	NOP%
Totals	17	30	56.67%	5	9	55.56%	6	9	66.67%	6	12	50.00%
> 1 lacs and <= 3 lacs	5	9	55.56%	2	3	66.67%	2	4	50.00%	1	2	50.00%
> 3 lacs and <= 5 lacs	6	11	54.55%	1	4	25.00%	2	2	100.00%	3	5	60.00%
> 5 lacs and <= 10 lacs	6	10	60.00%	2	2	100.00%	2	3	66.67%	2	5	40.00%

Thanks in advance.