topic Re: Expression not matching with dimension in bar chart in QlikView

Expression not matching with dimension in bar chart

jarrad_murray — Tue, 22 May 2018 12:42:43 GMT

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

Re: Expression not matching with dimension in bar chart

ogautier62 — Tue, 22 May 2018 15:05:07 GMT

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

Re: Expression not matching with dimension in bar chart

vishsaggi — Tue, 22 May 2018 15:11:16 GMT

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?

Re: Expression not matching with dimension in bar chart

jarrad_murray — Tue, 22 May 2018 15:47:46 GMT

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.

Re: Expression not matching with dimension in bar chart

jarrad_murray — Tue, 22 May 2018 15:50:59 GMT

Hi Vishwarath,

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

Re: Expression not matching with dimension in bar chart

vishsaggi — Tue, 22 May 2018 16:03:26 GMT

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

Re: Expression not matching with dimension in bar chart

jarrad_murray — Thu, 24 May 2018 08:30:47 GMT

Example File Attached

Re: Expression not matching with dimension in bar chart

jarrad_murray — Thu, 24 May 2018 08:31:43 GMT

Hi Vishwarath,

I have added an example file to the discussion thread.

Many thanks for your help with this.

Re: Expression not matching with dimension in bar chart

vishsaggi — Thu, 24 May 2018 16:20:24 GMT

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?

Re: Expression not matching with dimension in bar chart

jarrad_murray — Fri, 25 May 2018 07:52:09 GMT

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.

Re: Expression not matching with dimension in bar chart

vishsaggi — Fri, 25 May 2018 14:13:52 GMT

I will have to look into it again. Will get back later. ok.

Re: Expression not matching with dimension in bar chart

vishsaggi — Fri, 25 May 2018 14:51:36 GMT

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.

Re: Expression not matching with dimension in bar chart

jarrad_murray — Mon, 04 Jun 2018 11:59:03 GMT

Hi Vishwarath,

Many thanks for this solution, I have applied it and it works as expected.

Thank you for all your help.

Jarrad