Easy & Efficient way to derive unique/distinct value list within Xcelsius/Dashboards

[former_member190855]

In this post, I will explain how to extract the distinct values from an array of repeating values within Xcelsius. It is very easily done in standalone excel file using Advance filter or with Remove duplicate rows controls. However, these are not an option when within Xcelsius. While developing a dashboard it is required to calculate the value dynamically at runtime. Following is how you can achieve this.

In the example data, original list looks like in column O and desired output is as in column R

We are going to find for each row -  if the particular value exists in the previous rows in the list. If it does, we mark it as zero (0) else with 1. For the first value in the row we will hard code it to 1.

For the following rows, I used the combination of MATCH, ISERROR and IF functions. The MATCH function looks for the lookup_Value say UK in row 2 within O1:O1 and, CANADA in O3 is looked for in O1:O2, FRANCE in O4 is looked for in O1:O3. and so on.

If it find a match MATCH function returns the row number else #N/A. The ISERROR function determines #N/A as error TRUE and otherwise FALSE. FALSE indicates the value in current row is repeating hence IF returns 0. If ISERROR calculates TRUE it means the value is occurring for the first time in the row and we mark with 1 with IF. Lets take a look at the formula (in column P)

P1=1

P2==IF(ISERROR(MATCH(O2,$O$1:$O1,0)),1,0).....

....

P20==IF(ISERROR(MATCH(O20,$O$1:$O19,0)),1,0)

Result should look like the following screenshot. As you can see, each distinct value has been marked 1 only once - the first occurrence in the array.

Next step is to take advantage of the "Filtered Rows" insertion type in Xcelsius selectors. I added a combobox and mapped the

Labels to $P$1:$P$20,

Source data to $O$1:$O$20

Destination to $R$1:$R$10

Insertion Type = Filtered Rows

Behavior tab: Common: Selected Item

Type= Label

Item = $T$2 ( hardcoded to 1 and made sure not changed by any other components)

This will ensure that the combobox will always filter the rows with value 1 in column P and copy them in column R

Next lets map columns P,Q,R to a spreadsheet and preview to test our result.

As seen, our mission is successfully achieved. Now to protect the user from changing combobox selection from 1 to 0 we need to hide this behind a background component. Do not set dynamic visibility to hide this because the combobox should stay active to filter the rows.

Hope a lot of your issues to get distinct values get resolved using this method. Notice, we didn't need any COUNTIF or VLOOKUP formula.

You could use any other selectors with Filtered Rows option available but combobox is the easiest to hide - hence the choice. Also, if it is possible to get the distinct values directly from database without much hassle, thats recommended over any excel formula specially if the original list is too long.

Please Like my post and share if you find it useful. Would love to hear your thoughts and any alternate solution.

Thanks

Runali