topic comparing data in QlikView

comparing data

Tue, 08 Sep 2009 20:34:38 GMT

hi all,

I've looked over the forum but didnt find the exact example for what I want,although that I found something similar

I coudn't manage to perform it... [:^)]

Pls look at the file. many user are comparing data between 2 or more periods/versions but what I'm looking for is that:

for example the user is selecting "version=1" and then the column data in the chart is changing according to his selections

BUT I need that the near by column (called 'to compare' ) will not change...well thats easy-set analysis...BUT the user want to select ANOTHER

period / version lets (say version=3) and that his selection will not effect column data but only effects the column 'to compare'.

Again,two selection -one is for the data and the other selection influence on the column 'to compare' only.

(the need is for comparing to the data that has selected before hope its clear..)

* I think that I need to add another column with the same values of version,but I'm not sure......

thants is advance

David

comparing data

Tue, 08 Sep 2009 21:21:04 GMT

First, you can use Set Analysis to ignore the selection. On your second expressions, use:

sum({1} sales)

That will keep the second column the same regardless of your selection in version. Test that out and see how that works in your situation.

As for the second selection, you may have a few options. You may want to pull in the selection choices for the second selection, but make sure they are not linked to the rest of your data. Then when you make a selection for that second option, you could use a variable along with Set Analysis to filter the data.

Set your variable up as:

=GetFieldSelections(Selection2Field)

Then your Set Analysis on the second expression would become something like:

sum({1<Field = {$(=vVariable)}>} sales)

That Set Analysis function may need to be altered depending on what type of data you have (for example putting quotes around the dollar sign expansion for string values).

comparing data

Tue, 08 Sep 2009 21:27:54 GMT

Hi David,

As you suggest, I believe it is best to create another field to select the comparison value. I did a few changes to your example, see attached.

There is a separate table for the second version field, version_2.
The chart uses set analysis to ignore the version selection in the second column, and use version_2 instead.

Hope this helps,

Hellmar

comparing data

johnw — Wed, 09 Sep 2009 06:06:31 GMT

If your actual case is as simple as the example, with just a version field that you want to be different between the columns, then I'd probably do what Hellmar suggests and have a version_2 field and use set analysis on it for the second column.

But I'm guessing that in the general case, where you instead want to compare a complicated selection of multiple values in multiple fields, this could be done most easily with one or more bookmarks. Using bookmarks, you can easily prepare two different sets to compare. Make selections for the first set and click a button to bookmark. Make selections for the second set, and you can easily compare to the bookmarked set using set analysis. If you want two different sets being compared, and yet a third set of selections for the rest of the reports, then two bookmarks.

comparing data

Wed, 09 Sep 2009 19:23:32 GMT

first-thanx everyone

Hellmar you got me right and your solution is great and working.

John,your comment is logical and I will appreciate if you could add an example for it

in the file (version)

comparing data

johnw — Thu, 10 Sep 2009 05:50:05 GMT

Gah, I figured you might ask for that.

See, I never touch bookmarks myself. I've never dealt with bookmarks in macros or in set analysis. So far, I can't seem to make the idea work, even if it seems sound to me.

I do seem to be able to save a bookmark:

sub AddToCompareBookmark
ActiveDocument.RemoveUserBookmark "ToCompare"
ActiveDocument.CreateUserBookmark "ToCompare"
end sub

But this doesn't seem to be making a user bookmark, but rather a server bookmark. I suppose that's fine, since supposedly server bookmarks are the replacement for user bookmarks. However, I can't seem to see server bookmarks, so it isn't doing me much good. I can manually create a user bookmark and name it the same thing. I can verify that the bookmark is there and working by simply clicking on it. But when I use it in a set analysis expression:

sum({BM01} sales)
sum({ToCompare} sales)

It doesn't return anything different than sum(sales). I assume I'm just doing something wrong, but that's about all the help I'm getting out of the help text for set analysis.

Anyone want to take a crack at this? It SHOULD be a trivial problem to solve.

comparing data

johnw — Thu, 10 Sep 2009 23:53:31 GMT

Well, it turns out I'm not the only one having problems getting user bookmarks to work with set analysis. So for now, I've changed the example to use document bookmarks. It now demonstrates what I was suggesting. I do see one minor problem, which is that the chart doesn't refresh - QlikView apparently doesn't check for charts using bookmarks when a bookmark changes. I consider that a bug, but there's probably some easy workaround, some macro command to tell it to rebuild the chart.

comparing data

prieper — Fri, 11 Sep 2009 03:41:51 GMT

Hi John
just tested and for me this works, provided you include your bookmark in "".

Peter

comparing data

johnw — Fri, 11 Sep 2009 03:51:53 GMT

So you modified the user bookmark one to add double quotes, and it worked for you? Is this the change you made?

sum({"ToCompare"} sales)

Because that's not working for me either.