Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this:
=Sum(Aggr(If(SubID = Above(SubID) and ID = SubID, Above(Actual)), SubID, ID))
Would it be possible with set analysis?
Unless you are willing to make changes in the script, I doubt set analysis is possible here
If it is simple transformation then I would not mind
Also when I select the ID from listbox value becomes 0
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)
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)
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?
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?