Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a Pivot table above: 2nd Expression "%" is where I need to get correct values. This colm sud give me Exp1 for ea Vendor divided by total of all vendors within ea Group. So "%" value for Vendor 1 sud have been = 13/29 ~ 45%. How do I get that?
In general I am trying to pull Top 5 vendor data within Group.
For Exp1 I am using sum(aggr(if((rank(count({ < fieldA = {'1'}>} ID))) <=5,count({< FieldA = {1}>} ID)),Group, Vendor))
For % I am using Exp1/count( TOTAL <Group>{< FieldA = {1}>} ID) but (this one is not working)
May be this:
Sum(Aggr(If((Rank(Count({<fieldA = {'1'}>} ID))) <= 5, Count({<FieldA = {1}>} ID)), Group, Vendor))/
Sum(TOTAL <Group> Aggr(If((Rank(Count({<fieldA = {'1'}>} ID))) <= 5, Count({<FieldA = {1}>} ID)), Group, Vendor))
May be this:
Sum(Aggr(If((Rank(Count({<fieldA = {'1'}>} ID))) <= 5, Count({<FieldA = {1}>} ID)), Group, Vendor))/
Sum(TOTAL <Group> Aggr(If((Rank(Count({<fieldA = {'1'}>} ID))) <= 5, Count({<FieldA = {1}>} ID)), Group, Vendor))
Thats correct! sunny.
Taking that one more step ahead; If I want the denominator part of the % to be all Vendors within the Group, not just top 5, what wud the Expression be then?
For Eg. Group A has 6 vendors, with total count of 29+1 equal to 30. and I want the result to be 13/30, 4/30, and so on..
how it sud be handled?
May be this:
Sum(Aggr(If((Rank(Count({<fieldA = {'1'}>} ID))) <= 5, Count({<FieldA = {1}>} ID)), Group, Vendor))/
Sum(TOTAL <Group> Aggr(Count({<FieldA = {1}>} ID), Group, Vendor))
Thank you!