Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
educastri83
Contributor

Help!! with Bar chart with AGGR Dimension

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
 

 

example.png

1 Solution

Accepted Solutions

Re: Help!! with Bar chart with AGGR Dimension

Is this what you are trying to get?

image.png

Expression

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

Re: Help!! with Bar chart with AGGR Dimension

HI @sunny_talwar maybe help me with this!!

Re: Help!! with Bar chart with AGGR Dimension

Is this what you are trying to get?

image.png

Expression

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

Re: Help!! with Bar chart with AGGR Dimension

Yes it is! thank very much Sunny!

Re: Help!! with Bar chart with AGGR Dimension

No problem at all :).
educastri83
Contributor

Re: Help!! with Bar chart with AGGR Dimension

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:

Example 2.png

 

Re: Help!! with Bar chart with AGGR Dimension

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

educastri83
Contributor

Re: Help!! with Bar chart with AGGR Dimension

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
Contributor

Re: Help!! with Bar chart with AGGR Dimension

@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"


  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

 

 

work around (2).png

Re: Help!! with Bar chart with AGGR Dimension

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