Help needed with countIf and Contains formulas

Question & Answer
,
1

Hello, I need help with a calculated formula. In my dataset, I have the following structure:

alias (string) | widget_name (string) | alias_chain (string)
trobert | widget name 1 | trobert,rjohn,wright,etc.
pmike | widget name 2 | trobert,rjohn,wright,etc.
lkelly | widget name 2 | trobert,rjohn,wright,etc.
etc.

I would like to add a calculated field to count the total number of widgets for each Alias found in the alias_chain. In the example above, trobert shows up on three places, so trobert’s count in the new calculated field would be 3, pmike and lkelly’s count would be 0.

I tried the following formula which is working when searching value from a parameter, but when I replace it with alias, it doesn’t work.

countIf({widget_name}, contains({alias_chain}, {alias}))

Ultimately I would like the count to show up as another table column in the visual view as follows:

alias (string) | widget_name (string) | alias_chain (string) | count
trobert | widget name 1 | trobert,rjohn,wright,etc. | 3
pmike | widget name 2 | trobert,rjohn,wright,etc. | 0
lkelly | widget name 2 | trobert,rjohn,wright,etc. | 0

2

Hi @aescribens - Welcome to AWS QuickSight community and thank for posting the question. If I understood the problem statement correctly in your data alias column value needs to check each value of alias chain and the determine whether the value is present or not. If your alias chain is not constant, I do not think it is possible in QS, we have to handle this out of quicksight, possibly write an python script who will do a loop on the alias and then check each valie of alias chain and populate the count.

By saying this, let’s hear from other experts @David_Wong @duncan @ErikG @DylanM @sagmukhe @Naveed @Biswajit_1993 - Please provide your advise on this.

Regards - Sanjeeb

1 Like
3

That’s correct, to complicate this a bit further, some alias_chain values have no corresponding alias values, because these two tables are left joined, the table containing alias has many more records than the widgets table. As a result, this is an example of the Dataset:

alias (string) | widget_name (string) | alias_chain (string)
trobert | widget name 1 | trobert,rjohn,wright,etc.
pmike | widget name 2 | trobert,rjohn,wright,etc.
lkelly | widget name 2 | trobert,rjohn,wright,etc.
null | widget name 3 | tsusy,rjohn,wright,etc.
null | widget name 4 | harrym,johnm,peterk,etc
null | widget name 5 | trobert,rjohn,wright,etc.
etc.

This new example should result in trobert having a count of 4 in the Visuals Table column.

1 Like
4

Hi @aescribens - I believe this calculation should be done out of QS. Let’s wait and hear from other experts as well.

Regards - Sanjeeb

5

Hello @aescribens !

I agree with @Sanjeeb2022 that this might be better done outside of Quicksight.

That being said, you could try making the alias a parameter and apply it to your count if or an ifelse statement. Something like below:

${AliasParam} control would be linked to Alias field

Ifelse(
contains({alias_chain}, ${AliasParam}) = TRUE,
count{widget_name}, 
{Alias} = Null AND contains({alias_chain}, ${AliasParam}) = TRUE,
count{widget_name}
Null)
1 Like
6

Thanks Duncan, I’m fairly new to QS, so please bear with me on this. I believe your suggested solution of creating a parameter would only address the count for a single alias?

7

Hello @aescribens, that is correct but this is mostly a workaround so that you can attempt to keep the logic in QS.