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

SCNDocUnique1.png

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)

SCNDocUnique2.png

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.

SCNDocUnique3.png

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)

SCNDocUnique4.pngSCNDocUnique5.png

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.

SCNDocUnique6.png

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

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Arun Kumar

    Nice trick!! But on what circumstances you get duplicate records in Xcelsius? You can handle it in Query browser and Webi reports before bring it in dashboard itself.

    (0) 
  2. Runali Ghosh Post author

    Thanks Ryan for your comment!

    Arun, yes ideally one should try to get the distinct list from database/webi but there are situations like calculating aggregation on one dimension from a dataset with 2 dimensions may require this. Because of decreasing performance with adding one more query in the query browser or BIWS connectors – if the dataset is not too large as I mentioned in the post, this may do some tricks.

    (0) 

Leave a Reply