Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

jayabedin
New Contributor III

Excel Formatting Issues

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

4 Replies
Lech_Miszkiewicz
Honored Contributor III

Re: Excel Formatting Issues

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

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
jayabedin
New Contributor III

Re: Excel Formatting Issues

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.

Lech_Miszkiewicz
Honored Contributor III

Re: Excel Formatting Issues

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

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
jayabedin
New Contributor III

Re: Excel Formatting Issues

Hi Lech,

I added the NUM functioned accordingly. The result in the Qlik looks OK but once export to Excel Format changes.

Find the attachment for details please.

Community Browser