Divide value by the total of the column and compare

[nesonica]

Hi guys,

I have a column field named "category"

in this "category" i have the values: "good", "medium", "bad".

1)

I want to see how high the share of the "category" is per each dimension "customer" in percent ("good" 30%, "medium" 60%, "bad" 10%)

2) 

Then I want to compare the share of a customer ("good" 30%) compared to the average customer ("good" 70%) and give the text information "the customer x is y % below average in the category z)

I hope I did not express it too complicatedly. Thanks in advance and sorry for the bad wording. 🙂

[dplr-rn]

Use

Sum(Sales)/Sum(Total <Category> Sales)

out below

Customer	Category	Sum(Sales)	Sum(Sales)/Sum(Total <Category> Sales)
Peter	A	100	25.00%
Larry	A	300	75.00%
John	A	0	0.00%
Peter	B	400	100.00%
Larry	B	0	0.00%
John	B	0	0.00%
Peter	C	500	20.00%
Larry	C	900	36.00%
John	C	1100	44.00%

[hopkinsc]

Hi, What about the attached. 

[nesonica]

I would love to check it out, but unfortunately I have no rights to upload qvw in our QlikView . Could you please share the formulas with me? Thank you 🙂

[hopkinsc]

Hi,

[Customer Share]

sum(Sales)/Sum( total <Category> Sales)

Average

avg(total <Category>aggr(sum( {<Sales-={0}>}Sales)/Sum( total <Category> {<Sales-={0}>}Sales),Sales,Category))

Difference

[Customer Share]
-
Average

Advice

=if(Difference<=0,
'Category '&Category &' is '&num(Difference,'#,##0.00%') & ' under average',
'Category '&Category &' is '&num(Difference,'#,##0.00%') & ' above average'
)

Note that the 'Difference' expression use the previous expression names of  [Customer Share]  and [Average] and the 'Advice' expression uses the [Difference] expression name.

See attached image of result