filter on page loop in excel

[amien]

is this possible?

I have 1 main sheet with all the data.

In the next sheets I want to loop though a certain dimension. But I don't want to create a sheet for all dimensions. Only for the dimensions with a value of > 0 in 2015. I don't want to apply this filter on the main sheet.

[Stephen_Jasionowski]

As of now, filtering at the page level is not available but is on the roadmap.

In the interim, here is a possible solution:

1. create the "total" sheet in your excel template

2. copy the sheet and and then apply the page loop for the dimension of your choice

3. create a filter for values of that dimension > 0 and for the year 2015. note that it may be easiest to do this as a QlikView bookmark and then import the bookmark as a filter (How to Create QlikView Bookmark Filters Using Dynamic Search Expressions)

4. apply the filter to all objects on the sheet with the page loop (How to Apply a Specific Filter to Tables or Images)

Please let me know if this meets your requirement.

[amien]

Thanks Stephen,

When i apply the filter on all objects within the page loop sheet, the page itself will still be created right? It will only be empty? Or will the page itself also not be created when all the objects are empty (filter applied)

[Daniel_Jenkins]

Hi Amien,

Here is a link to an example project that:

* Does not need changes to the QVW (e.g. Bookmarks)

* First sheet has complete unfiltered data

* One subsequent sheet for each dimension of filtered data

* No sheet generated for a dimension if the filter does not return any data

Pages with Conditional ?

HTH - Daniel

[amien]

Thanks Daniel.

Still a few questions:

* Does this mean you can't apply any other filter on report level? So another filter beside Dept.

* Can i still filter some departments on report level. Yes this will effect the consolidated sheet. I think i just have to create not a clear selection filter, but a select excluded for example

[Daniel_Jenkins · Accepted solution]

Hi Amien,

* Yes you can. Including Department. Using the same sample project

Adding a filter for some Depts.

gives:

Sheet CONSOLIDATED has all because of Object level filter (filterBudget Clear). Sheets for Depts. H, I & J are filtered out by the Report level filter (filter). Sheet for B is filtered out by the Report level filter (filterBudget) that filters for sales over 350.00.

Adding another filter for some values of Sales

Gives:

Sheet CONSOLIDATED has all because of Object level filter (filterBudget Clear). Sheets for Depts. H, I & J are filtered out by the Report level filter (filter). Sheet for B is not filtered out because Report level filter (filter1) includes value 235.41 (Sales for Dept. B) and is applied after filterBudget and therefore overrides it (filter on same field).

* I suggest that you go ahead and give it a try. If you post a sample project (QVW, NSQ & Report template) and also a sample report showing what you expect, someone can take a look.

HTH - Daniel.