Indirect Set Analysis w/ Prev/Next ID

[karimeddini_sla]

Hi, I am trying to get the previous or next value within a group. While the results are correct in aggregate, the #s are not associating with the expected record. For each group, the value in "Next Value" should be associated with the previous row when using this expression SUM({$<id = P(id_next)>} value) . What am I missing (see attached for Load Script)?

EDIT: Though the example uses a table, the application does not (i.e: use of After/Before & Above/Below is not suitable).

[#2]

Hi Peter,

not sure I understand the exact output you are trying to achieve, but if you are looking at something similar to above but for the script, take a look at the previous or peek functions to get you on the right track in changing your script.

if you can expand a bit more on your expected output, I'll be able to help further

Hope that helps

Joe

[karimeddini_sla]

Every value in the Next Value column in the QVW should be 1 row higher than it actually is. I'm trying to have a row dynamically (not in Load Script) get the Value of the next row.

[#4]

Hi Peter,

you've said in your post this isn't going to be used in a chart, so previous etc isn't an option.

How is this actually going to be used in practice?

Are you just looking for an expression? If so how is this going to be used if not within a chart? Will it be within a set text box of some kind?

Basically some detail around your question would be helpful

thanks

Joe

[karimeddini_sla]

I said it won't be in a table. It will be in a chart.

Yes, I am looking for an expression, specifically a correction to the one in my original post. Within each GROUP_ID, for a given ID, I need to access the child records where ID = ID_NEXT (would be done with a self-join in SQL). the child records for a given ID, when summed give VALUE. NEXT VALUE should be VALUE for where ID = ID_NEXT.

So the below is the table in the QVW. What Iw ant is for "143" to be on the 1st row. The expression I wrote should be doing that from what I can tell (It is saying: for ID = 1, get the SUM of VALUE where ID=ID_NEXT=2), but it is instead putting it on the row of ID_NEXT.

[#6 · Accepted solution]

Hi Peter,

you said table but then said you don't want to use previous/above etc, functions which you can't use in a table, given that it's actually a chart within your example (and you called it a table), I assumed that you simply got mixed up between the two that's all.

Your current expression

SUM({$<id = P(id_next)>} value)

won't work I'm afraid, as the modifier with set analysis is done across the total expression and not on a row by row basis. The example you highlighted is selecting the possible values of id_next for the whole set, not per ID.

If as you have said, you can't use previous/above or some other in chart function, then I would suggest looking at a script change as you final option (using one of the functions I highlighted earlier).

Hope that helps

Joe

[karimeddini_sla]

BELOW(SUM(value)) gives what I am looking for if I only have group_id & id (in that order) as dimensions. However, the real solution needs to not care about the specifics of a table (it needs to work as a KPI and in a chart); you can see the total is NULL which is not correct.

[karimeddini_sla]

Okay, so upon selecting individual IDs and using a text object w/ the original expression, I do get correct results, which led to the realization that the dimensions are the problem. I cannot simply use TOTAL to ignore the dimensions, as that gives the SUM of all entries in the same group. Is there no way to "step" outside the dimension you're in for an expression? I guess I'm wishing for "Dimension Analysis", since I was treating Set Analysis as if it could forcibly set Dimension.

[martin_dideriks]

Try this:

Sum(

       Aggr(

            Below(sum({$<id>} value ),1 )

       ,id)

  )

Hope this helps and gives what you are asking for.

//Martin

[karimeddini_sla]

That expression spans group_id, which is where there are control breaks in this logic. Additionally, your solution is sensitive to the order and does not sue next_id to actually find the proper record.