4 Replies Latest reply: Nov 9, 2017 6:54 PM by Jaynal Abedin RSS

    Excel Formatting Issues

    Jaynal Abedin

      Hi,

       

      I am using NPrinting 17.3  I have a Pivot table in Qlikview, This is formatted accordingly in Qlikview. In NPrinting, I am keeping the source format.

       

      But once I put this into NPrinting Excel, format changes. I am definitely missing something. Any help on this will be highly appreciated. Please find attachment that shows QV  and NPrinting output.

       

      Regards

      Jay

        • Re: Excel Formatting Issues
          Lech Miszkiewicz

          Well,

           

          From what i see formats are kept as per your request - they are the same (the same decimal points, the same number or % formats etc).

          The problem you have is that excel automatically recognizes text values and aligns them to the left. Text values will be the ones which have in the same column % and numbers. The reason behind this is a formula you have used in QlikView to build an expression ( i believe you used num function for the column where mismatches are shown)

           

          Can you post an expression you have used in your pivot table or if possible qvw file? I suggest you also test it first in QlikView by exporting this table to Excel (sent to Excel option).

           

          the hint i can give you is to keep NUM function outside in your expression (i think you have it inside like:

          "Pick(RowNo(),

               num(expre1,'##0.0'),

               num(expre2,'##0.0%')

          )"


          so you have to change it to something like this:

          num(

          Pick(RowNo(),

              expre1,

              expre2,)

          Pick(RowNo(),

              '##0.0',

               '##0.0%')

          )

           

           

          hope this helps

          regards

          Lech

            • Re: Excel Formatting Issues
              Jaynal Abedin

              Hi Lech,

               

              Thank you very much. You are in the right direction.

              My syntax are: like :

               

              Pick(Match(ActivityDescriptions,

              'P1 Median Mobilisation Time',

              'P1 90th Percentile Mobilisation Time',

              'P1 percentage within 3 minutes',

              'P2 Median Mobilisation Time',

              'P2 90th Percentile Mobilisation Time'

              ),

               

              $(vP1Mob50PercL24m),

              $(vP1Mob90PercL24m),

              Num($(vP13MinMobCasesL24m)/$(vP1MobCasesL24m),'###.##%'),

              $(vP2Mob50PercL24m),

              $(vP2Mob90PercL24m)

              )

              I need this right justified.

                • Re: Excel Formatting Issues
                  Lech Miszkiewicz

                  so yeah,

                   

                  try putting num in front of your Pick(Match... like this

                  num(

                  Pick(Match(ActivityDescriptions,

                  'P1 Median Mobilisation Time',

                  'P1 90th Percentile Mobilisation Time',

                  'P1 percentage within 3 minutes',

                  'P2 Median Mobilisation Time',

                  'P2 90th Percentile Mobilisation Time'

                  ),

                   

                  $(vP1Mob50PercL24m),

                  $(vP1Mob90PercL24m),

                  $(vP13MinMobCasesL24m)/$(vP1MobCasesL24m),

                  $(vP2Mob50PercL24m),

                  $(vP2Mob90PercL24m)

                  ),

                  Pick(Match(ActivityDescriptions,

                  'P1 Median Mobilisation Time',

                  'P1 90th Percentile Mobilisation Time',

                  'P1 percentage within 3 minutes',

                  'P2 Median Mobilisation Time',

                  'P2 90th Percentile Mobilisation Time'

                  ),

                  '###.##',

                  '###.##',

                  ,'###.##%',

                  '###.##',

                  '###.##'

                  )


                  I know it looks silly, but i had similar issue and this was a solution....not elegant though....


                  let me know how it goes..