topic Re: In Pivote table if row wise values are less then column total then Red else Green how to achieve in App Development

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

P_Kale — Tue, 06 Feb 2024 17:05:29 GMT

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.

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

Nagaraju_KCS — Tue, 06 Feb 2024 18:20:04 GMT

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

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

kaushiknsolanki — Tue, 06 Feb 2024 18:16:57 GMT

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

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

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

P_Kale — Wed, 07 Feb 2024 05:45:42 GMT

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

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

P_Kale — Wed, 07 Feb 2024 05:46:33 GMT

Thanks @Nagaraju_KCS

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.

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

Anil_Babu_Samineni — Wed, 07 Feb 2024 07:04:46 GMT

@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.

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

P_Kale — Wed, 07 Feb 2024 17:29:32 GMT

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.

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

kaushiknsolanki — Thu, 08 Feb 2024 15:56:19 GMT

Here is your solution.

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

P_Kale — Sun, 11 Feb 2024 14:56:36 GMT

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.