Bucket Dimension

[kyt_karen]

Hi

I am trying to create a dimension based on the values of different measures. The idea is to categorize companies according to their type of purchases. Each Company has multiple rows with different values on each of these columns, which is why I would like the category based on the SUM of these values. Nevertheless, I get empty values for all companies.

=Aggr(IF sum([JG portfolio value])>0.00 AND sum([P Portfolio Value])>0.00, 'JG AND P',
if (sum([JN Portfolio Value])>0.00 AND sum([P Portfolio Value])>0.00, 'JN AND P',
if (sum([J Portfolio Value])>0.00 AND sum([P Portfolio Value])>0.00, 'J AND P',
if (sum([P Portfolio Value])>0.00 AND sum([JG portfolio value])=0.00 AND sum([J Portfolio Value])=0.00 AND sum([JN Portfolio Value])=0.00, 'Only P',
if (sum([P Portfolio Value])=0.00 AND sum([JG portfolio value])>0.00 AND sum([J Portfolio Value])=0.00 AND sum([JN Portfolio Value])=0.00, 'Only JG',
if (sum([P Portfolio Value])=0.00 AND sum([JG portfolio value])=0.00 AND sum([J Portfolio Value])>0.00 AND sum([JN Portfolio Value])=0.00, 'Only J',
if (sum([P Portfolio Value])=0.00 AND sum([JG portfolio value])=0.00 AND sum([J Portfolio Value])=0.00 AND sum([JN Portfolio Value])>0.00, 'Only JN', 'other')))))))

thanks in advance 

[chrismarlow]

Hi,

I think to use in a dimension you need to put AGGR around each sum, rather than at the front, with your dimensions to aggregate over as well, so if there was just one dimension 'Company' it would be similar to below;

=if (aggr(sum([JN Portfolio Value]),Company)>0.00 AND aggr(sum([P Portfolio Value]),Company)>0.00, 'JN AND P',
if (aggr(sum([J Portfolio Value]),Company)>0.00 AND aggr(sum([P Portfolio Value]),Company)>0.00, 'J AND P',
if (aggr(sum([P Portfolio Value]),Company)>0.00 AND aggr(sum([JG portfolio value]),Company)=0.00 AND aggr(sum([J Portfolio Value]),Company)=0.00 AND aggr(sum([JN Portfolio Value]),Company)=0.00, 'Only P',
if (aggr(sum([P Portfolio Value]),Company)=0.00 AND aggr(sum([JG portfolio value]),Company)>0.00 AND aggr(sum([J Portfolio Value]),Company)=0.00 AND aggr(sum([JN Portfolio Value]),Company)=0.00, 'Only JG',
if (aggr(sum([P Portfolio Value]),Company)=0.00 AND aggr(sum([JG portfolio value]),Company)=0.00 AND aggr(sum([J Portfolio Value]),Company)>0.00 AND aggr(sum([JN Portfolio Value]),Company)=0.00, 'Only J',
if (aggr(sum([P Portfolio Value]),Company)=0.00 AND aggr(sum([JG portfolio value]),Company)=0.00 AND aggr(sum([J Portfolio Value]),Company)=0.00 AND aggr(sum([JN Portfolio Value]),Company)>0.00, 'Only JN', 'other'))))))

Thanks,

Chris.

[kyt_karen]

Thanks a lot! that worked! So just to wrap this up, by adding the Company dimension, this would be an quivalent to a fixed statement in Tableau? (as in, fixing it to the Company level?). If so, for future formulas, could I include 2 dimensions to be fixed? e.g. ..........if (aggr(sum([P Portfolio Value]),Company,Product)=0.00 AND aggr(sum([JG portfolio value]),Company,Product)=0.00 AND aggr(sum([J Portfolio Value]),Company,Product)=0.00 AND aggr(sum([JN Portfolio Value]),Company,Product)>0.00, 'Only JN', 'other'))))))

thanks again!