Solved: Re: Expression not matching with dimension in bar ... - Qlik Community

jarrad_murray · ‎2018-05-22

Hi All,

I am experiencing something very strange with a bar chart. I have one expression value that isn't joining up with a dimension but is still part of it.

The bar on the right hand side is the value for the 'Planned Care' dimension but is showing as not part of anything. If I select planned care I still get the same but the value is the only one showing.

I don't get this issue with any of the other values I have within my data, just this one. The expression and value are joined by a primary/foreign key relationship which is populated direct from teh table.

Does anyone have any idea what might be causing this or where I may have gone wrong?

Many thanks

Jarrad

vishsaggi · ‎2018-05-25

I am still looking into why $- is removing all the instances of the departmentkeys. Quiet not sure. However, for now we have a work around to use this expr in your charts. So rather than removing projects approved using $- i used condition inside the set removing -1 for those project names. Do the testing and see if that works for you. Attaching the app.

Expr used: = Count({$< Project_Name = {"= Project_Approved <> '-1'"} >} Project_Name)

I will have to further look into why that does not work. I believe we have Nulls for those department keys for whom the projects were not assigned. As the department and projects are linked based on key department key, you are finding the key 7 when you count projects.

View solution in original post

ogautier62 · ‎2018-05-22

Hi,

verify your link between the two tables,

it seems that your table with amounts is not populated with the right value of 'Planned Care'

regards

vishsaggi · ‎2018-05-22

I think its showing total of your dimension. Can you screenshot your Dimensionlimits tab in the chart properties and see if you have checked Show others?

jarrad_murray · ‎2018-05-22

Hi Olivier,

The link between my two tables appears fine (see snapshots below). The value in the project table gets populated from a drop down off of the department table. All of the other departments match up fine just this one. I have checked even overwritten the value from one table to the other but still get this error.

jarrad_murray · ‎2018-05-22

Hi Vishwarath,

I do have the 'Show Others' option checked but deselecting it doesn't make any difference.

vishsaggi · ‎2018-05-22

Is possible to share the file you are working on to have a quick look?

jarrad_murray · ‎2018-05-24

Example File Attached

jarrad_murray · ‎2018-05-24

Hi Vishwarath,

I have added an example file to the discussion thread.

Many thanks for your help with this.

vishsaggi · ‎2018-05-24

based on your expr: = Count({$-<Project_Approved = {'-1'}>} Project_Name)

What does '$-' is doing here in your expr is removing those depts whose projects approved is -1? Right correct me?

So the projects which do not have -1 for projects approved we want to count them, means in the below screenshot the deptKeys 7,4,10 we have 2 depts with null Project approved and two (of which one is -1 and another is -) project approved. So when you use your expr to take away projects whose projects approved is -1 we are losing deptkey - 7, hence you are getting Null for that Department. Correct me here?

jarrad_murray · ‎2018-05-25

Hi Vishwarath,

You are correct, the '$-' is to exclude all projects with an approved of '-1'. I have tried changing the expression to = Count({<Project_Approved -= {'-1'}>} Project_Name) but this doesn't work.

I don't quite follow your response, are you saying that because I have a -1 for an existing deptkey 7 that I will lose all other instances of this?

Conversley, If I change the department to departmentKey the expression aligns as expected.

Expression not matching with dimension in bar chart