How to remove % column in front of total column in Pivote table

[P_Kale]

Hi,

Count({< EARLY_NON ={'EARLY','NON EARLY'}>} POLICY_NO)/Count(TOTAL <FY_YEAR> {< EARLY_NON ={'EARLY','NON EARLY'}>} POLICY_NO) * 100

through above formula i am able to calculate % row wise against total but the formula is getting applied to total column also and 100 is appearing in front of all the rows. I not want this % column in total. How I can remove this.

EARLY_NON	Totals		EARLY		NON EARLY
FY_YEAR	NOP	%	NOP	%	NOP	%
Totals	28200	100	13759	58.69129	14441	41.30871
FY_2015-2016	12000	100	2982	47.26581	9018	52.73419
FY_2016-2017	3400	100	2809	51.70256	591	48.29744
FY_2017-2018	5000	100	2207	53.85554	2793	46.14446
FY_2018-2019	6000	100	2953	70.67975	3047	29.32025
FY_2019-2020	1800	100	2808	81.9854	-1008	18.0146

Thanks in advance

@marcus_sommer 

@swuehl 

@sunny_talwar 

@Anil_Babu_Samineni 

[P_Kale]

Hi @Anil_Babu_Samineni Thanks for your time.

Below attached is the application and required excel files.

Thanks in advance.

[Anil_Babu_Samineni]

@P_Kale PFA, Have  a look this object title "Original with Valuelist"

With this approach, you don't need this table and can be removed. 

Total_Inline:
Load * Inline [
Dim
1
2
3
];

Output:

[P_Kale]

Hi @Anil_Babu_Samineni 

I have used the application given by you and applied the logic provided by you. Now, thing is I am not getting "% total column" but % which are getting calculated are not correct. 

In the sample QVD also % are not correct. Correct % are highlighted in blue.

	Claim_Type	EARLY			NON EARLY			Total
FY_YEAR	Measure	NOP	NOP%	NOP% correct	NOP	NOP%	NOP% correct	NOP
Totals		26	39.40%	86.7%	4	6.10%	13.3%	30
2015-16		5	7.60%	55.6%	4	6.10%	44.4%	9
2016-17		9	13.60%	100.0%	-	-		9
2017-18		12	18.20%	100.0%	-	-		12

[Anil_Babu_Samineni]

@P_Kale You can do like this for both NOP%.

/Count(Total [CHDRNUM])

/count({<Claim_Type={'EARLY','NON EARLY'}>} [CHDRNUM])

[P_Kale]

Hi @Anil_Babu_Samineni Thanks for your inputs. 

From sample application if i have removed "count(TOTAL [CHDRNUM])" then it is working fine.

but in real scenario below given are the observations 

In new formula i have removed  "TOTAL <FY_YEAR>" but then nothing is being get displayed. Table is blank.

New and old both the formulas are given below.

Below given is the New Formula as suggested by you.

If(ValueList('EARLY','NON EARLY','Total') ='EARLY' and Measure='NOP', Count({< EARLY_NON ={'EARLY'}, CONSIDER_NOT_CONSIDER = {'CONSIDER'} >} POLICY_NO),
If(ValueList('EARLY','NON EARLY','Total')='EARLY' and Measure='NOP%', num(Count({< EARLY_NON ={'EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>} POLICY_NO)/Count({< EARLY_NON ={'EARLY','NON EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>} POLICY_NO),'##.0%'),
If(ValueList('EARLY','NON EARLY','Total')='NON EARLY' and Measure='NOP', Count({< EARLY_NON ={'NON EARLY'}, CONSIDER_NOT_CONSIDER = {'CONSIDER'} >} POLICY_NO),
If(ValueList('EARLY','NON EARLY','Total')='NON EARLY'and Measure='NOP%', num(Count({< EARLY_NON ={'NON EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>} POLICY_NO)/Count({< EARLY_NON ={'EARLY','NON EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>} POLICY_NO),'##.0%'),
If(ValueList('EARLY','NON EARLY','Total')='Total'and Measure='NOP', Count({< EARLY_NON ={'EARLY','NON EARLY'}, CONSIDER_NOT_CONSIDER = {'CONSIDER'} >} POLICY_NO),
If(ValueList('EARLY','NON EARLY','Total')='Total'and Measure='NOP%', NULL()
))))))

Old Formula:-  

If(ValueList('EARLY','NON EARLY','Total') ='EARLY' and Measure='NOP', Count({< EARLY_NON ={'EARLY'}, CONSIDER_NOT_CONSIDER = {'CONSIDER'} >} POLICY_NO),
If(ValueList('EARLY','NON EARLY','Total')='EARLY' and Measure='NOP%', num(Count({< EARLY_NON ={'EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>} POLICY_NO)/Count( TOTAL <FY_YEAR> {<EARLY_NON ={'EARLY','NON EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>} POLICY_NO),'##.0%'),
If(ValueList('EARLY','NON EARLY','Total')='NON EARLY' and Measure='NOP', Count({< EARLY_NON ={'NON EARLY'}, CONSIDER_NOT_CONSIDER = {'CONSIDER'} >} POLICY_NO),
If(ValueList('EARLY','NON EARLY','Total')='NON EARLY'and Measure='NOP%', num(Count({< EARLY_NON ={'NON EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>} POLICY_NO)/Count(TOTAL <FY_YEAR> {<EARLY_NON ={'EARLY','NON EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>} POLICY_NO),'##.0%'),
If(ValueList('EARLY','NON EARLY','Total')='Total'and Measure='NOP', Count({< EARLY_NON ={'EARLY','NON EARLY'}, CONSIDER_NOT_CONSIDER = {'CONSIDER'} >} POLICY_NO),
If(ValueList('EARLY','NON EARLY','Total')='Total'and Measure='NOP%', NULL()
))))))

[Anil_Babu_Samineni]

@P_Kale Just curious how data model linked for these two fields? and what is the base key for them (field names are, "EARLY_NON" and "CONSIDER_NOT_CONSIDER")

[P_Kale]

Hi @Anil_Babu_Samineni Thanks for help.

"EARLY_NON" this field is coming from 1 table which is joined on Policy No. with Master policy table.

"CONSIDER_NOT_CONSIDER" is a calculated column below given is the condition 

if(DATE_OF_ISSUE >= '01/04/2015' and DATE_OF_ISSUE <= '31/03/2024','CONSIDER','DO_NOT_CONSIDER') as CONSIDER_NOT_CONSIDER,

[P_Kale]

@Anil_Babu_Samineni thanks for your time.

Have you able to analyse what is exactly happening in the query, 

[Anil_Babu_Samineni]

@P_Kale My suggestion to use Sum(Aggr(<Your Expression>, <Your Dim1>, <Your Dim2>, ...))

I assume the field "CONSIDER_NOT_CONSIDER" is associated to field "Policy No".

[P_Kale]

Hi @Anil_Babu_Samineni Thanks for your valuable time and helping me.

I have added aggr as given below. But expression is giving error stating " Aggr takes at least 2 parameters". Since I have not used Agg function before can you pl help to correct syntax.

If(ValueList('EARLY','NON EARLY','Total')='EARLY' and Measure='NOP', count({<EARLY_NON={'EARLY'}>} POLICY_NO),
If(ValueList('EARLY','NON EARLY','Total')='NON EARLY' and Measure='NOP', count({<EARLY_NON={'NON EARLY'}>} POLICY_NO),
If(ValueList('EARLY','NON EARLY','Total')='EARLY' and Measure='NOP%', Num(sum(Aggr({< EARLY_NON ={'EARLY','NON EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>} POLICY_NO))/sum(Aggr({< EARLY_NON ={'EARLY','NON EARLY'},CONSIDER_NOT_CONSIDER = {'CONSIDER'}>},<FY_YEAR> POLICY_NO),'##.0%')
))))