How to use the rank argument in the Max() function when there are duplicates in the data set

[jorgett]

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

[Kashyap_R]

hi

can u share some sample data with all the fields in that dataset

thanks

[Kushal_Chawda]

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;

[jorgett]

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.

[Kushal_Chawda]

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

[jorgett]

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

[rwunderlich]

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

[marcus_sommer]

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

[jorgett]

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

[jorgett]

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