topic Re: How to use the rank argument in the Max() function when there are duplicates in the data set in Visualization and Usability

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

jorgett — Sat, 16 Nov 2024 01:33:11 GMT

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,

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

Kashyap_R — Thu, 09 Jan 2020 16:05:33 GMT

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

thanks

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

Kushal_Chawda — Thu, 09 Jan 2020 16:05:41 GMT

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;

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

jorgett — Thu, 09 Jan 2020 16:22:04 GMT

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.

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

Kushal_Chawda — Thu, 09 Jan 2020 16:53:31 GMT

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

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

jorgett — Thu, 09 Jan 2020 17:45:07 GMT

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,

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

rwunderlich — Thu, 09 Jan 2020 22:37:28 GMT

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

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

marcus_sommer — Fri, 10 Jan 2020 09:43:04 GMT

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

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

jorgett — Fri, 10 Jan 2020 13:55:53 GMT

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,

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

jorgett — Fri, 10 Jan 2020 14:14:48 GMT

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,

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

marcus_sommer — Fri, 10 Jan 2020 15:35:56 GMT

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

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

jorgett — Fri, 10 Jan 2020 16:14:27 GMT

Marcus,

I noticed when I do

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]))))

, it is ignoring the middle values. The calculation is performed for every other cell, but the ones in the middle (highlighted in red in the table below). It instead gives "-" for their corresponding cell. See table below.

1	8	9
2	6	8
2	6	8
2	5	7
4	4	-
4	4	-
5	2	7
6	2	8
6	2	8
8	1	9

I am using your exact code, I copied and paste into it and that's the result it gave me. Thanks for the help.

-JF

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

marcus_sommer — Sat, 11 Jan 2020 11:46:44 GMT

It's strange, it shouldn't happens … especially within a rangesum() which treats non-numeric values or NULL as zero so that if there are no numeric parameter the result should be zero, too.

This means that at least one of the parameters is invalid in the given context and prevent a correct working of the entire rangesum() so that they returned NULL (the hyphen is just a replace-char for NULL).

It's difficult to say what's causing this effect … In general I would check if there are NULL's (real NULL's, empty values, spaces, any other kind of invalid values) within the field - I assume it's rather not relevant for this example but it might cause problems and/or erroneous respectively unexpected results.
This means you need to consider strategies how to handle them - this could be the replace of them per NULL variables/mapping or any other kind of replacing (for example with a string like 'Null' or ''NV' or maybe just with 0) or to exclude them from your further calculations (maybe within a set analysis).

More likely seems to be that the pick-index failed in any way - especially if there is more than a single dimension in the table and/or there are conditions applied on the dimensions and/or there are further selections within the application. In all these cases need the rowno(total) and the count() separately checked if they return the expected values. If not it could become difficult to adjust them maybe further set analysis and/or aggr() could be used - but easier would be to simplify the objects/logics.

Another thought goes to the formatting of the fieldvalues - if they are different to the default number-format and/or this has a comma as decimal-delimiter it's troublesome. Therefore I suggest to check this, too and to apply some of the following approaches:

alt(num(floor(num#(keepchar(trim([Column A]), '0123456789,.'), 'ConvertFormat')), 'OutPutFormat'), 0)

to ensure a certain format/data-type.

- Marcus

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

jorgett — Mon, 13 Jan 2020 16:35:58 GMT

Hi Marcus,

My apologies. I checked your code again and it is working fine. I was the one who copied it incorrectly. Thank you for your help! It's greatly appreciated.

Best,