How to consider only the full Steps values of entity (Id) ?

[yanivvl0]

hi to all ,

we have in our model Id's , each of them can reach the steps 1 or 1+2 or 1+2+3 or 1+2+3+4 .    ( step 4 is the finsh ).

a step is not done Unless there is the next step for the Id. ( excep step 4 ).

for each step there is a value.

we need to sum just steps ended ( excep step 4 ) , for example : if  an Id have 1,2,3  steps - the sum is for the values of steps 1,2

not 3 .

take a look :

the total sum is 2440 BUT it shuld be ( by is calculate ? )  1600 .

So , how can we do that ? need your great help ..

another one  : how can we sum the values of step 1 - only if there is step 2 ?

Thanks!

[Anonymous]

Hi

go with Prevous() function on the column "is calcluate"?+

and sum it up..

[sunny_talwar · Accepted solution]

May be this:

=Sum({<Key = {"=Aggr(Max(TOTAL <Id> Step), Id, Step) = 4 or Aggr(Max(TOTAL <Id> Step), Id, Step) > Step"}>}Value)

Where Key is created in script like this:

Table:

LOAD AutoNumber(Id&Step) as Key,

  *;

LOAD * INLINE [

    Id, Step, Value

    10, 1, 100

    10, 2, 200

    10, 3, 40

    20, 1, 300

    30, 1, 50

    30, 2, 150

    30, 3, 300

    30, 4, 100

    40, 1, 200

    40, 2, 400

    50, 1, 500

    50, 2, 100

];

[Anonymous]

Hi Yaniv,

You can calculate it on script:

Data:
LOAD * INLINE [
    Id, Step, Value
    10, 1, 100
    10, 2, 200
    10, 3, 40
    20, 1, 300
    30, 1, 50
    30, 2, 150
    30, 3, 300
    30, 4, 100
    40, 1, 200
    40, 2, 400
    50, 1, 500
    50, 2, 100
];

LEFT JOIN (Data)
LOAD
    Id,
    Count(DISTINCT Step) as CountSteps,
    MAX(Step)             as MaxStep
RESIDENT Data
Group By Id;

Aux:
LOAD *,
    If(Step = MaxStep,If(CountSteps = 4,'Y','N'),'Y') as Calculate;
LOAD *
RESIDENT Data;

DROP TABLE Data;

Then, your expression is more simple: Sum({<Calculate={'Y'}>} Value)

If you want to aggregate steps value (two examples, all values and values by Id), then calculate Aux table like this:

//Aux table for aggregate by Id steps value
Aux:
LOAD *,
     IF(Peek(Id) = Id,
         If(Calculate = 'Y',Value+Peek(AggrValue),Peek(AggrValue)),Value) as AggrValue;
LOAD *,
    If(Step = MaxStep,If(CountSteps = 4,'Y','N'),'Y') as Calculate;
LOAD *
RESIDENT Data
ORDER BY Id asc, Step asc;

//Aux table for aggregate all calculated steps values
Aux:
LOAD *,
     IF(IsNull(Peek(Id)),Value,
         If(Calculate = 'Y',Value+Peek(AggrValue),Peek(AggrValue))) as AggrValue;
LOAD *,
    If(Step = MaxStep,If(CountSteps = 4,'Y','N'),'Y') as Calculate;
LOAD *
RESIDENT Data
ORDER BY Id asc, Step asc;

Regards!

[yanivvl0]

thanks Sunny - much help again !!

[yanivvl0]

hello Manuel - thank you for helping us !!