topic Banding - loop report over possible values in field in QlikView

Banding - loop report over possible values in field

Anonymous — Mon, 17 Jan 2011 18:44:32 GMT

This banding feature is good but I have one issue. My "dimensional attribute" that I am "looping" over has members/rows that have no data for my current selection. This in itself is fine and "correct" but I dont want those dimensional members/rows printed on the report. In the properties of the table/chart that I have in my report i have:

1) under dimensions -> suppress value when is null and

2) under presenetation -> suppress zero value in all columns

The second option i suspect only works if there is zero values, for a row, in all measures but i have not tested it. Anyway, neither option suppresses banded rows that do not have associated data. Because i dont see these values in my table with current selections, I assumed the report would not print them either but such does not appear to be the case.

Any help would be appreciated. Thanks.

Banding - loop report over possible values in field

Anonymous — Tue, 18 Jan 2011 19:24:43 GMT

bump!

Banding - loop report over possible values in field

rwunderlich — Tue, 18 Jan 2011 19:36:51 GMT

Can you post a simple example qvw?

-Rob

Banding - loop report over possible values in field

Anonymous — Tue, 18 Jan 2011 19:42:31 GMT

Lol, yes but it will take a wee bit to make. My actual application is anything but simple :).

Banding - loop report over possible values in field

Anonymous — Tue, 18 Jan 2011 21:01:17 GMT

Rob, i added a qvw attachment to my previous post. It has one report. If you open the report and click "print preview" you will see the report. The first page that comes up is for a person I called NODATA. There are no transactions for this person but sadly the banding featture does not seem to respect the associative technology and filter out dimensional members that have no facts as does the table that this report is based on does. The Banding feature associated with the report is under report settings -> selections tab -> Banding)

Banding - loop report over possible values in field

rwunderlich — Tue, 18 Jan 2011 23:55:55 GMT

Tim,

One solution is to utilize a bookmark with a search expression that filters out the zero people. In your example,

=sum(Transactions)>0

I assigned this to bookmark "MyBookmark" and assigned it in the Report Settings -> Selections tab.

Example attached. See if that gives you what you want.

-Rob

Banding - loop report over possible values in field

Anonymous — Wed, 19 Jan 2011 08:38:45 GMT

Thanks Rob. That looks like it will work just fine. I guess i just thought that the associative "technology" would filter out those dimensional members that were not related to facts in the data context of the report. The "extra" dimensional members are related to facts but historical ones. So, in a current report, many of them will not be involved.

Banding - loop report over possible values in field

Anonymous — Wed, 19 Jan 2011 18:36:26 GMT

As it turns out I am using my current selections in the report. This reports is run by a macro and first activates a sheet that has a trigger setting the "date" conditions. If I use a bookmark, it will no longer set the correct date conditions. Boomarks and Current Selections are mutually exclusive options it would seem.

Banding - loop report over possible values in field

rwunderlich — Wed, 19 Jan 2011 20:14:31 GMT


Tim Webber wrote: Boomarks and Current Selections are mutually exclusive options it would seem.

No. Set the "Make Bookmark Apply on top of Current Selections" property in the bookmark to make the bookmark additive. For an existing bookmark, it's the "+" column available from the Bookmarks -> More menu,

-Rob

Banding - loop report over possible values in field

Anonymous — Thu, 20 Jan 2011 05:20:49 GMT

That works! Thanks. Never used an expression as a search condition. Pretty cool but my application is getting full of little gotcha's or rather bandaids!

This report is launched by a macro. My next challenge is to capture a darn error message. One of the filters i apply in the macro results in zero data - for now. So when i try and print the report Qlikveiw complains:

Caption reads: Empty Reprot

Body of dialogue box:

The report will not be printed

Error Message

No possible values for field <mytable.myfield> used for banding.

Super big bummer. I'll work on this next.

Banding - loop report over possible values in field

rwunderlich — Tue, 25 Jan 2011 06:44:36 GMT

The Evaluate method may be useful here.

If ActiveDocument.Evaluate("GetPossibleCount(mytable.myfield") > 0 Then
' Print the report
End If

-Rob

Banding - loop report over possible values in field

Anonymous — Wed, 26 Jan 2011 01:08:10 GMT

Nice Rob. I will try this because it seems like i might be able to create a more generic row condition test with this method. What i did was create a variable and a document trigger that populates the variable with an expression. I retrieve the value of that variable in the script and do a test to see if I can print or not (much like your test). Problem with my method is that the "on change" document trigger requires you to specify the dimension that when changed, updates the expression. It works but I have to create all these extra triggers for each possible dimension that i want to use as a filter.

I'll try your suggestion and report back :).