Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
Say I have the following data set called "Data": 1, 2, 2, 2, 4, 4, 5, 6, 6, 8.
When I use Max(Data, 1) I get 8. Max(Data, 2) = 6, BUT Max(Data, 3) = 5. This makes sense, but I wanted Max(Data,3) to equal to 6 since that is the next number in the sequence and for Max(Data, 4) = 5, Max(Data, 5) = 4 and so on. If there's no way around this, do you have any idea of how I can go through the data set from the largest value to the lowest value without ignoring the duplicate values? Doing this in script is not an option for what I am doing and rowno() does not apply either.
Please help.
Thanks,
JF
I think you could get this view by creating a calculated dimension in a table like:
valueloop(1, count(total [Column A]), 1)
and adding a bit more logic to the already mentioned expression like:
rangesum(
pick(rowno(total), $(=concat([Column A], chr(44), -[Column A]))),
pick(count(total [Column A]) - rowno(total) + 1, $(=concat([Column A], chr(44), -[Column A]))))
Further if you want to do it with multiple fields it might be useful to replace the static field-reference of [Column A] with something like: [$(=$Field)] or maybe with an own fieldlist (loaded with a small inline-table) and selecting it with a listbox.
- Marcus
hi
can u share some sample data with all the fields in that dataset
thanks
How about this?
Data:
Load * Inline [
Number
1
2
2
2
4
4
5
6
6
8 ];
Final:
NoConcatenate
Load *,
RowNo() as Rank
Resident Data
Order by Number desc;
Drop Table Data;
Doing this in script is not an option for me. I wanted to create a KPI where I will be adding the 1st number to the 10th number, then add the 2nd to the 9th, 3rd to 8th, 4th to 7th, 5th to 6th, 6th to 5th, 7th to 4th and so on. After I have that column, I will sum it all up to get my KPI. Here's an example.
Column A | Column A in Reverse order | Adding Column 1 to Column 2 |
1 | 8 | 9 |
2 | 6 | 8 |
2 | 6 | 8 |
2 | 5 | 7 |
4 | 4 | 8 |
4 | 4 | 8 |
5 | 2 | 7 |
6 | 2 | 8 |
6 | 2 | 8 |
8 | 1 | 9 |
My KPI would give me the following: 9+8+8+7+8+8+7+8+8+9 = 80. The number 80 is what my customer is interested in. Again, doing this calculation in script is not an option for me. Thanks.
I am confused and not sure what you are trying to achieve, but don't you think that Sum(Column A)*2 will give you that KPI number?
Please post some sample data with expected output for better understanding
Sorry for the confusion. Yes, I know how to sum up column "Adding Column 1 to Column 2". What I do not know is how to get to column "Adding Column 1 to Column 2". I want to be able to come up with a way of adding the 1st num to the 10th num, 2nd to the 9th, 3rd to the 8th and so on. If that is still confusing essentially what I want is for someone to use this data set,
Data: Load * Inline [ Number 1 2 2 2 4 4 5 6 6 8 ];
and get this result
Column A | Column A in Reverse order | Adding Column 1 to Column 2 |
1 | 8 | 9 |
2 | 6 | 8 |
2 | 6 | 8 |
2 | 5 | 7 |
4 | 4 | 8 |
4 | 4 | 8 |
5 | 2 | 7 |
6 | 2 | 8 |
6 | 2 | 8 |
8 | 1 | 9 |
without any manipulation in the script. the only thing you are allowed to do in script is to load the data. The table you are to compute it all in charts. That's the challenge.
Thanks,
JF
Do you have a fixed number of values, that is, always 10? Or is the problem more like first+last, (first+1) + (last-1), etc?
-Rob
I think you could use something like this:
= rangesum(pick(1, $(=concat(Data, chr(44), -Data))), pick(10, $(=concat(Data, chr(44), -Data))))
means to concat all values within a string and then to pick up the wanted ones. I assume that Data isn't just a field else the result of any calculation and then you will need an aggr() to wrap the expression and to apply the needed dimensions.
Like Rob mentioned if there aren't always 10 values you might need further logic to pick the right values from the list.
- Marcus
Hi Rob,
I do not have a fixed number of values; it varies. It is "more like first+last, (first+1) + (last-1), etc".
Thanks,
JF
Hi Marcus,
Thank you for your input. The number of values are fixed and I am working with fields. I am trying to perform a calculation with a field. The table here was a visual example of what I am working with since I cannot share the data. The equivalent of the field I am working with is "Column A" in the table in my previous posts. Since I will be doing this calculation with different field, the number of values vary.
Thanks,
JF