5 Replies Latest reply: Feb 4, 2016 4:22 PM by Daniel Jenkins RSS

    filter on page loop in excel

    Amien 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.

        • Re: filter on page loop in excel
          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.

            • Re: filter on page loop in excel
              Amien 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)

                • Re: filter on page loop in excel
                  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

                    • Re: filter on page loop in excel
                      Amien 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

                        • Re: filter on page loop in excel
                          Daniel Jenkins

                          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.