Dynamic Pareto / ABC (with a twist)

[Anonymous]

Hello all,

I am trying to come up with a formula in Sense that gives the Pareto- / ABC-class of orders as a dimension. While something like

If(RangeSum(Above(Sum(Meas1), 0, RowNo())) / Sum(TOTAL Meas1) <= 0.8, 'A', If(RangeSum(Above(Sum(Meas1), 0, RowNo())) / Sum(TOTAL Meas1) <= 0.95, 'B', 'C'))

works well as a measure when the orders are there as a dimension and I can sort them there, I am looking for a way to integrate the sorted order Dimension into the ABC-class (or something similar?). Showing individual orders themselves as dimension does not make sense because there can be thousands of them.

I have tried different combinations of Aggr() but the sorting does not seem to work, so my results so far have been wrong.

Example:

Aggr(If(RangeSum(Above(Sum(Meas1), 0, RowNo())) / Sum(TOTAL Meas1) <= 0.8, 'A', If(RangeSum(Above(Sum(Meas1), 0, RowNo())) / Sum(TOTAL Meas1) <= 0.95, 'B', 'C')), (Dim1, (Sum(Meas1), DESCENDING)))

I suspect I need more / nested Aggr() functions or to put them in different places?

Any help would be greatly appreciated -- Jens

[sunny_talwar]

Try this

Aggr(

If(RangeSum(Above(Sum(Meas1), 0, RowNo())) / Sum(TOTAL Meas1) <= 0.8, 'A',

If(RangeSum(Above(Sum(Meas1), 0, RowNo())) / Sum(TOTAL Meas1) <= 0.95, 'B', 'C'))

, (Dim1, (=Sum(Meas1), DESC)))

[Anonymous]

Thanks -- sadly this does not change the outcome, Dim1 still sorted by load order.

[Anonymous · Accepted solution]

Okay, apparently Sense is quite picky about how you actually construct the formula, it just does not say so in the editor.

This -- finally -- worked:

=Aggr(

If(RangeSum(Above(Sum(Meas1) / Sum(TOTAL Meas1), 1, RowNo())) <= 0.8, 'A',

If(RangeSum(Above(Sum(Meas1) / Sum(TOTAL Meas1), 1, RowNo())) <= 0.95, 'B',

'C')), (Dim1,(=Sum(Meas1), Desc)))

Moved the Sum(TOTAL ... ) into the Above() function and added exactly the right amount of parentheses in exactly the right places in the StructuredParameter. Why Sense does not complain when you write something that does not comply with the definition of a StructuredParameter I have no idea. Would have made things easier for me.

[sunny_talwar]

Might help to see a sample if you can share one?

[pablolabbe]

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others