topic Re: Stopping an expression from filtering on an unrelated dimension in QlikView

Stopping an expression from filtering on an unrelated dimension

Fri, 20 Jun 2014 12:00:46 GMT

Good morning!

I am having some trouble displaying the numbers I want to see in my application, and I believe that my solution will lie either in modified data design/linkages, or in expressions to limit the metrics that I'm pulling from my tables. Let me try to explain.

I have attached my table diagram for clarity and reference.

This is a pretty simple application where I have pulled in two cubes that were built for other purposes, and am trying to recreate some reports for those cubes in Qlikview. There is an "Rx" cube, and a "Calls" cube.

Rx is basically uniquePrescriberID, weekID, formID (product), and some metrics.

Calls is uniquePrescriberID, weekID, callTypeID, and some different metrics. There is no product (formID) in this cube.

I have set up a straight table with one dimension and two expressions.

Dimension = DimPrescriber.PValue

Expression 1 =SUM(CallsFact.callCount)

Expression 2 =sum(RxFact.T_DOLVolume)

When I do not select anything from my listboxes, my numbers all seem to look pretty good.

However, the problem I'm having is when I select a product (formID) from my Product Listbox. In this case, my Rx numbers filter correctly - but the Calls numbers also are filtering/changing somehow, which I do not want because CallsFact does not have formID in the table. It seems the app is inferring a relationship to formID through the link to RxFact (due to them both having weekID and uniquePrescriberID as keys) even though formID has absolutely no relevance to the Calls data.

So how can I best stop this from happening and keep my "=SUM(CallsFact.callCount)" expression static when I change items in the Product ListBox? Is it a question of smarter table design, or is there a kind of expression I can use to solve it?

Thanks!

Re: Stopping an expression from filtering on an unrelated dimension

Fri, 20 Jun 2014 13:31:40 GMT

I found a solution, though not sure if it's a better choice than any potential table design/linkage solutions.

I found the {$<field=>} set syntax in the documention, which I hadn't seen before. This expression seems to be getting me what I want to see:

=SUM({$<productGroupName=>} CallsFact.callCount)

Re: Stopping an expression from filtering on an unrelated dimension

swuehl — Fri, 20 Jun 2014 13:36:37 GMT

I think you can also consider concatenating your two fact tables.

edit: http://qlikviewnotes.blogspot.de/2009/11/understanding-join-and-concatenate.html

Re: Stopping an expression from filtering on an unrelated dimension

Fri, 20 Jun 2014 13:39:12 GMT

Thanks, I will look into this.

I was just thinking that the expression solution is not a good one, because if I add more listboxes for other fields that are not common between the 2 fact tables, I would need to add more and more field exceptions into the expressions for every single metric I put on every chart. That is not efficient at all.

Re: Stopping an expression from filtering on an unrelated dimension

Fri, 20 Jun 2014 14:22:59 GMT

I implemented the concatenation, and it does make my table diagram look a little simpler.

It's giving me a different (and quite logical) behavior for my =SUM(CallsFact.callCount) expression now, which is that it just displays zeros for every record when I choose an individual product from my listbox. While I suppose this is technically correct, it's not ideal - I would still like it to display as though no product was selected, regardless of what is chosen. While the SET logic I found does accomplish this, it doesn't seem ideal to me, since I would need to make more and more exceptions in every expression as I add more cubes and dimensions to the app.

Re: Stopping an expression from filtering on an unrelated dimension

Mon, 30 Jun 2014 11:44:23 GMT

I still have not found a solution for what I am looking to do - does anyone have any recommendations for a good reference on data design/table linkage strategies in Qlikview that I might be able to read through some examples to devise a better approach?

Re: Stopping an expression from filtering on an unrelated dimension

Tue, 04 Aug 2015 09:10:09 GMT

=SUM({1}CallsFact.callCount)

{1} means ignore filters

Should work for you.