How to hide irrelevant values in dimensions

[#1]

Hi,

I used your article for calculating Pareto :

1.My expression is:

=avg(Aging_Hours_Dashboard*60)

2.Accumulated:

= RangeSum(Above(avg(Aging_Hours_Dashboard*60), 0, RowNo()))

3.Inclusive Percentage

RangeSum(Above(Sum(Aging_Hours_Dashboard*60), 0, RowNo())) / Sum(total Aging_Hours_Dashboard*60)

4.Exclusive Percentage

= RangeSum(Above(Sum(Aging_Hours_Dashboard*60), 1, RowNo())) / Sum(total Aging_Hours_Dashboard*60)

4.Pareto Class

= if ([Exclusive Percentage]>0.02 and [Exclusive Percentage]<0.98,avg(Aging_Hours_Dashboard*60))

My dimension is Reason Code.

So I want to present bar chart only for the last column - Pareto Class.

I hid the previous three columns.

BUT: I don't know how to present only relevant dimensions values as also those dimensions for which Exclusive Percentage<=0.02 appear in the graph with NULL values. supress NULL values does not help as it refers to all column and i am interested only in Pareto class column as measurement.

[swuehl · Accepted solution]

Have you tried just using a single expression like

= if (RangeSum(Above(Sum(Aging_Hours_Dashboard*60), 1, RowNo())) / Sum(total Aging_Hours_Dashboard*60)>0.02 and RangeSum(Above(Sum(Aging_Hours_Dashboard*60), 1, RowNo())) / Sum(total Aging_Hours_Dashboard*60)<0.98,avg(Aging_Hours_Dashboard*60))

But if I understand your request correctly, you would need the Reason Code dimension values at both ends in your chart to calculate the rangesum correctly (at least the dimension values at the top). You are sorting the dimension by

=avg(Aging_Hours_Dashboard*60), right?

What you are ask for is then unfortunately not a trivial task, it would be possible to do with advanced aggregation and a sort by expression feature (see discussion in The sortable Aggr function is finally here!)

Other solutions may be possible using alternate states and pareto selects, e.g. starting from

Re: Identify count of vendors based on spend

Regards,

Stefan

[#3]

Hi,

actually your solution worked:)

I exchanged with your expression mines and then limited to 5 first values and supressed zero:)

Thanks a lot!

[swuehl]

Glad that it worked!

Have a nice day,

Stefan