Accumulation

[#1]

Hi,

I would like build a formula doing the same things than the "Full accumulation" option.

I cannot use this option because I need to use it in another formula.

If you have got any idea...please, let me know.

Eva

[Oleg_Troyansky]

something like this, assuming that Sum(Sales) is your original expression in Column(1):

rangesum(above(Column(1)), sum(Sales))

[Sokkorn]

Hi Eva,

Let do this expression:

=RangeSum(Above(TOTAL Sum(Sale),0,RowNo()))     OR =RangeSum(Above(TOTAL Sum(Sale),0,RowNo(TOTAL)))

Let me know.

Regards,

Sokkorn

[#4]

Hi,

Thanks  for our answers. I did a small example.

My final objective is to catch the first value of the dimension for "%Cumul" after 75% in another object.

For example:

if the selection is clear, I need "4" in a text object.

if the selection is 1 2 and 3  I need "2"  in a text object.

:S

[swuehl]

Eva, you want a kind of pareto analysis?

Maybe I am missing something easier, but this should work:

=min(aggr(if( rangesum( above(100/Count(total Metric)*Count(Metric),0,rowno()))>75,Dimension),Dimension))

assuming that your DImension load order is in the order that you need for the full accumulation (aggr() will sort the dimension values in load order, I believe).

See also attached.

[#6]

thanks, the formula is working. But I jhave too may rows to order them.  I'm looking for a way to do the same thing but with data disordered

[swuehl]

Eva, not sure if I understand.

In your example, your Dimension values are ordered 1 to 5.

Each dimension value is assigned a Metric (=count(Metric)), which can be arbitrary.

What do you mean with 'data disordered'? If you don't order your dimension in any controlled way, your sample (using the full accumulation) will show different outcomes. So how do you decide which result is the one you want?

[#8]

In fact, the formula works because data are loading in ascending order. but  in me real data it's no ordered and I cannot do it.

Please find attached the same example but in the script I juste switch the order.

[swuehl]

Not sure if I understand why you can't order your table in the script.

Well, in fact you don't need to order your (fact) table, it is sufficient to get the load order of the dimension you are using right. For example, let's assume you have only 1000 dimension values (1...1000), but your table has some hundred millions records, so you don't want to order the table.

So it's sufficient to create your dimension field first in your script with all values (could be even more than needed):

TMP:

LOAD

recno() as Dimension

autogenerate 1000;

Then load your large table LOAD ... FROM...; and then drop your first TMP table:

drop table TMP;

This is only to create Dimension with correct load order.

See attached.

Regards,

Stefan