4 Replies Latest reply: Dec 21, 2015 8:34 AM by Aran Nathanson RSS

    The formula is changed in NPrint excel template

    isaac li

      Hi All,

       

      I am using QlikView NPrint to send the excel report.

       

      In the excel template, I add two pivot tables objects IDs, CH13 CH14 and both of them have 10 rows.

       

      And I want to use their values to calculate in excel, and I add =C15 into one cell which is the 5th row of the CH14.

       

      But when I get the excel from Nprint, that formula is changed to =C25 instead of C15.

       

      I guess, that formula reference is changed, how I can avoid that.

       

       

      In summary, my requirement is I would like to use the result from Pivot table which is generated by Nprint, and based on that result I can add some formula in that excel.

       

       

       

      Thanks.

        • Re: The formula is changed in NPrint excel template
          jagan mohan rao appala

          Hi,

           

          Since the new rows are added to the Excel file while generating the report, the formula will change based on the rows inserted, so if the rows are static like 10 then you can directly give =C25 instead of =C15.

           

          Hope this helps you.

           

          Regards,

          Jagan.

          • Re: The formula is changed in NPrint excel template
            Mike Czerwonky

            I agree with Jagan that you have to account for the additional rows.  Is it possible to combine the two pivot tables into one objects in QV with your desired output?  You would still have the ability to add formulas in Excel but there would be less variables.

              • Re: The formula is changed in NPrint excel template
                Adam Krepistman

                is the formula working correctly?  Qlikview does not export povot tables as true pivots so ther layout in nprinting is different than the layou in the Qlikview applicaiton, which probably accounts for the formula change

                 

                The templates are designed to be dynamic in order for the formula to work, regardless of the number of reos/columns

                 

                you may also notice that with a pivot table, when added to the editor, you do not get to move individula columns into the template like a straight table, so you do not ahve as much control over each column/cell to create formulas, but if your formulas or working, that is good to hear

                you may need to create as a straight table in qlik 9maybe make a copy and hide it), bring into nprinting as a straignt table and then convert to pivot table in excel, due to the nprinting pivot limitations

              • Re: The formula is changed in NPrint excel template
                Aran Nathanson

                Unless you want to write a macro in Excel, the way to do this is to build an Excel pivot table with the fields in your QlikView pivot tables:

                 

                1. Take your pivot tables in QlikView, clone them and convert the clones into straight tables in QlikView.
                2. Follow this tutorial which will explain how to load the data and populate an Excel pivot table:

                How to Create a Pivot Table from Excel Table Columns

                 

                You'll have to log onto community to see the tutorial.

                 

                Best

                 

                Aran