Logic help needed

[#1]

I have below data set.

ID	SubID	Actual
1	1	100
1	2	200
1	3	250
2	2	300
2	3	400
3	3	500
3	4	600

Output needed (I want to calculate it in front end only)

ID	Value
1	0
2	200
3	400

Logic: For ID 2, I want to see previous ID & subid should be equal to current id

          example, For ID 2, I will see the previous ID 1 and Pick subid =2 value

[sunny_talwar]

May be this:

=Sum(Aggr(If(SubID = Above(SubID) and ID = SubID, Above(Actual)), SubID, ID))

[#3]

Would it be possible with set analysis?

[sunny_talwar]

Unless you are willing to make changes in the script, I doubt set analysis is possible here

[#5]

If it is simple transformation then I would not mind

[#6]

Also when I select the ID from listbox value becomes 0

[sunny_talwar]

May be like this:

Table:

LOAD *,

  AutoNumber(ID&SubID) as Key;

LOAD * INLINE [

    ID, SubID, Actual

    1, 1, 100

    1, 2, 200

    1, 3, 250

    2, 2, 300

    2, 3, 400

    3, 3, 500

    3, 4, 600

];

Left Join (Table)

LOAD ID + 1 as ID,

  SubID,

  Actual as Prev_Actual

Resident Table

Where ID + 1 = SubID;

And then just this as your expression -> Sum(Prev_Actual)

[sunny_talwar]

You can use this expression to address the issue of getting zero when you select an ID

=Sum(Aggr(If(Only({1}SubID) = Above(Only({1}SubID)) and Only({1}ID) = Only({1}SubID), Above(Only({1}Actual))), SubID, ID)) * Avg(1)

[#9]

Hi sunny,

but my requirement is dynamic. lets say currently I am showing for previous but there would be previous to previous and up to 5 step back. So when I will select the step as 1 above calculation will work and when I will select step 2 below will be output

ID	Value
1	0
2	0
3	250

How would I do it in front end?

[sunny_talwar]

Where is the input coming from? Are there set number of inputs like Step 1 to Step 10? Also, are we discarding the backend solution and moving onto just the front end solution?