Help!! with Bar chart with AGGR Dimension

[educastri83]

Hi everyone!!

 I have a dashboard to identify users who repeat their passport number to be able to detect if a passport number has different display name.

In a pivot chart I managed to differentiate a list only with those users using a calculated dimension = aggr (if (count (DISTINCT [Display Name])> = 2, [Passport Number]), [Passport Number]) it Works.

The challenge is to be able to visualize a bar chart that shows only the display name that have the previous condition and that counts the system roles that each user has. I know I can offer the option of a list filter to select those cases, but what I need is to set the bar chart only with those users, not with all

Spoiler

Spoiler

Spoiler

[educastri83]

HI @sunny_talwar maybe help me with this!!

[sunny_talwar · Accepted solution]

Is this what you are trying to get?

Expression

Count (DISTINCT {<[Passport Number] = {"=Count(DISTINCT [Display Name]) >=2"}>} [System Rol])

[educastri83]

Yes it is! thank very much Sunny!

[sunny_talwar]

No problem at all :).

[educastri83]

hi @sunny_talwar I just detected that there may be repeated users who are the same person (but for data cleasing issues..) they have different first name and last name. (the repeated user seems to have added their middle name and second last name). How can I create a validation for when these cases happen?

Maybe something in the script?

Actually:

[sunny_talwar]

I am not sure what exactly are you asking for? What would you want QlikView to do here?

[educastri83]

Maybe something like this thread:  https://community.qlik.com/t5/QlikView-App-Development/First-Name-Last-Name/td-p/1325126 but im not sure.

is something I have not done before, but I would like to separate the first text string from the "First name" field and the first text string from the "Last name" field as a new field, so that I can display the "Display name" in the dashboard " to review

[educastri83]

@sunny_talwar I solved it by means of a work around, I tried to split the fields "First Name" and "Last Name", and from that result I concatened them to be able to have an alternative value of "Display Name"

in the script add the following:

  upper (SubField ([First Name], '', 1)) & '' & upper (SubField ([First Name], '', 0)) as NewName,
      upper (SubField ([Last Name], '', 1)) & '' & upper (SubField ([Last Name], '', 0)) as NewLastName,
      upper (SubField ([First Name], '', 1)) & '' & upper (SubField ([First Name], '', 0)) & Upper (SubField ([Last Name], '', 1)) & '' & Upper (SubField ([Last Name], '', 0)) as NewDisplayName

I feel that it is not the most optimal solution, but it allows me to calculate the KPI:
Dimension: NewDisplayName
Expression: count (DISTINCT [Display Name])

Note: Everything that is> 2 then is what I need to have differentiated to review it

[sunny_talwar]

Very nice, I am glad you were able to take it to the next level.