topic Re: How to highlight bucket values into Red if above total values in that column in pivote table in App Development

How to highlight bucket values into Red if above total values in that column in pivote table

P_Kale — Tue, 06 Feb 2024 08:01:00 GMT

Hi,

I have to highlight bucket wise values into "Red" if above or equal to total of that column like attached below in summary.

Can you pl help to achieve this. I have used below 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%')

Thanks in advance.

@marcus_sommer

@sunny_talwar

@Anil_Babu_Samineni

Re: How to highlight bucket values into Red if above total values in that column in pivote table

Anil_Babu_Samineni — Wed, 07 Feb 2024 07:08:32 GMT

@P_Kale Can you please share QVF from sample excel file to check.

Re: How to highlight bucket values into Red if above total values in that column in pivote table

P_Kale — Wed, 07 Feb 2024 10:19:18 GMT

Thanks @Anil_Babu_Samineni

Pl find the QVF and out-put required in excel. If the values are >= total then should be in red.

Thanks in advance.

Re: How to highlight bucket values into Red if above total values in that column in pivote table

Anil_Babu_Samineni — Wed, 07 Feb 2024 11:17:11 GMT

@P_Kale thanks for that, Like mentioned by kaushik in another thread, In general it never be Total high that the values from model 🙂

If you see anything and you need that to be Red() just let us know highlighting them from your data.

Re: How to highlight bucket values into Red if above total values in that column in pivote table

P_Kale — Wed, 07 Feb 2024 11:50:43 GMT

Hi @Anil_Babu_Samineni Thanks.

Actually, I want the out-put as shown below where % values are in the red if more than Total % of that column.

% I have calculated row wise so you may have row wise % grater than column wise total %.

For e.g. Income Bucketing > 5 lacs and <= 10 lacs have more early claim % i.e. 60%.

Thanks in advance.

FY_YEAR	Totals						2015-16						2016-17						2017-18
Income_Bucketing	NOP	EARLY	%NOP	Premium_in lacs	EARLY_PREMIUM	% Premium	NOP	EARLY	%NOP	Premium_in lacs	EARLY_PREMIUM	% Premium	NOP	EARLY	%NOP	Premium_in lacs	EARLY_PREMIUM	% Premium	NOP	EARLY	%NOP	Premium_in lacs	EARLY_PREMIUM	% Premium
Totals	30	17	56.7%	1195	778	65.1%	9	5	55.6%	395	235	59.5%	9	6	66.7%	352	219	62.2%	12	6	50.0%	448	324	72.3%
> 1 lacs and <= 3 lacs	9	5	55.6%	306	176	57.5%	3	2	66.7%	65	45	69.2%	4	2	50.0%	178	76	42.7%	2	1	50.0%	63	55	87.3%
> 3 lacs and <= 5 lacs	11	6	54.5%	429	200	46.6%	4	1	25.0%	163	23	14.1%	2	2	100.0%	77	77	100.0%	5	3	60.0%	189	100	52.9%
> 5 lacs and <= 10 lacs	10	6	60.0%	460	402	87.4%	2	2	100.0%	167	167	100.0%	3	2	66.7%	97	66	68.0%	5	2	40.0%	196	169	86.2%

Re: How to highlight bucket values into Red if above total values in that column in pivote table

Anil_Babu_Samineni — Fri, 16 Feb 2024 14:17:19 GMT

@P_Kale As i have given workaround to another thread, Re: How to remove % column in front of total colum... - Page 2 - Qlik Community - 2413002

I guess, from the reference of that, You can think achieving this because the Total is not default settings in the example given from the link provider.

If you see any difficulty, Please build the same and share us the QVF to assist, so that we can have a look them!