Custom Formatting of Measure

[Fareeha123]

I have a measure with below calculation. Based on Currency selection we are showing values and format as well. When JPY is selected the number should not show decimal places. It is working in Qlik but same property is not being passed to Pixel Perfect NPrinting. 

Is there a way in conditional format rule where we can define same rule.

If Currency ='JPY' then '$#,##0', else '$#,##0.00'. As you can see in Image Subtotal should show whole number with comma separated because currency selected in filter is J'PY' and rows above should show numbers with 2 decimal places because base currency is USD

I am adding both the formulas.

1. Below is used for subtotal

if(GetSelectedCount([Currency])=0 or Currency='USD', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_USD),'$#,##0.00'),
if(Currency='EUR', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_EUR),'$#,##0.00'),
if(Currency='GBP', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_GBP),'$#,##0.00'),
if(Currency='JPY', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_JPY),'$#,##0'),
if(Currency='CNH', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_CNH),'$#,##0.00'),
if(Currency='CAD', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_CAD),'$#,##0.00'),
if(Currency='CHF', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_CHF),'$#,##0.00'),
if(Currency='AUD', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_AUD),'$#,##0.00')

))))))))

2. Used in the table for 4 and 5 column

if( BASE_CURRENCY='JPY',

If(
Len(Only(OpenFlag)) > 0,
Only(OpenFlag),
Num(sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}PRIOR_NET_ASSETS_BASE),'#,##0')

),
if(
Len(Only(OpenFlag)) > 0,
Only(OpenFlag),
Num(sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}PRIOR_NET_ASSETS_BASE),'#,##0.00')
))

We have Feb 2025 SR2 nprinting version. 

[Lech_Miszkiewicz]

HI @Fareeha123 

I see couple of options.

1. If you are not calculating totals in Pixel Perfect report template but instead you are just passing values calculated in Qlik then you can just wrap your expression in Qlik in Text() function - 

Text(

if(GetSelectedCount([Currency])=0 ..... etc RestOfYourExpression

)  so it is already formatted as number directly in Qlik and only ready to use value is being transferred with proper format simply as a string.

2. On the other hand if this is done in the pixel perfect template layer then you would need to have conditional hide/show sections with different formats visible depending on condition. Based on your description I think you should be ok with just text() around your formula.

One thing to add to it would be for you to create Master Measure and make those adjustments to master measure as it will just flow through to NPrinting otherwise you will have to go through very painful process of recreating pixel perfect levels each time you change your measure expression (if measure is just naked expression). Thats just a good practice when working with NPrinting in case you are not doing it.

cheers

[Fareeha123]

Thanks Lech.

Yes I created master item in qlik for all calculation. Also, Part of your solution worked and part is still missing. Rows in table are working as i applied text function on 2md formula shared above. Decimal for other surrency and no decimal for JPY is also working 1st formula for subtotal but, subtotals are showing coming without thousand separator. Although I have defined the format in the formula. I am assuming this could be because i am doing sum(group) on subtotal column and it is overwriting the text() function in Qlik ? 

How can apply thousand separators on both conditions.  

Please see image below for Subtotal in USD and JPY. 

[Lech_Miszkiewicz]

Hi @Fareeha123 

so as I mentioned in my previous comment there are 2 ways of doing this. Since you are actually doing pixel perfect based subtotals instead of creating subtotal dimension directly in Qlik (just like you would do with typical P&L like solution and grouping dimensions) then the only way I can see this working in pixel perfect is creating 2 differently formatted sections with subtotals and and showing one or another based on passed parameter. 

This is very old post but first half of it shows you the principle. https://community.qlik.com/t5/Member-Articles/How-to-Manage-PixelPerfect-Conditional-Output-Formats-.... In nutshell what it is is conditional hide/show for part of the report. This part of the report needs to exists as separate section and in your case you will have to be able to pass to it condition via field value or variable which you would have to include in it too (does not have to be visible but has to be present) for it to work.

I recognise that this solution adds complexity but will do the job.

Lastly - I would actually reconsider and redesign your source table in Qlik Sense so it already has subtotals in it in single object which can be treated just like another row of data. 

cheers

[Fareeha123]

I am calculating subtotal column in Qlik table as a column. That is then used in the subtotal part of pixel perfect. Below is the formula used to create measure for subtotal in Qlik Table. We have to apply aggregation on subtotal or total to show values in Pixel perfect table. If i remove the sum aggregation on subtotal, then I cannot see the values. 

text(if(GetSelectedCount([Currency])=0 or Currency='USD', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_USD),'$#,##0.00'),
if(Currency='EUR', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_EUR),'$#,##0.00'),
if(Currency='GBP', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_GBP),'$#,##0.00'),
if(Currency='JPY', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_JPY),'$#,##0'),
if(Currency='CNH', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_CNH),'$#,##0.00'),
if(Currency='CAD', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_CAD),'$#,##0.00'),
if(Currency='CHF', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_CHF),'$#,##0.00'),
if(Currency='AUD', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_AUD),'$#,##0.00')

)))))))))

[Lech_Miszkiewicz · Accepted solution]

Hi @Fareeha123 

As mentioned there are 2 options:

1. move your aggregation to Qlik Sense and treat it as a single table with grouping dimension. For example: Currently you are using Fund Name and Fund Number and Currency as dimensions. I will assume that fund number can be treated as a key so if you create extra columns in data model which would link to  to that field in following way you could used Fund Number Grouped, Fund Name Grouped and Currency grouped as dimensions which would create subtotal as well

that is very common method of grouping data to create subtotals and is based on the same principles you would use in P&L when grouping account to create custom subtotals. Then such table can be simply used as a table in pixel perfect and does not require pixel perfect subtotals

2. if you would like to stick to what you have then as I already provided you with the links you would need to create 2 versions of subtotals each with different required number format and conditionally hide/show them with depending on currency. 

Maybe first option seems more complicated but in fact it is not so I would still do that in data model.

cheers

[Fareeha123 · Accepted solution]

Thanks Lech, 

This is the final solution that we achieved

Created formula in pixel perfect nprinting with text function. Dragged this formula in Subtotal section and it is working as expected. No need to create 2 formula/fields and hide and show. 

text(

if(GetSelectedCount([Currency])=0 or Currency='USD', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_USD),'$#,##0.00'),
if(Currency='EUR', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_EUR),'$#,##0.00'),
if(Currency='GBP', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_GBP),'$#,##0.00'),
if(Currency='JPY', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_JPY),'$#,##0'),
if(Currency='CNH', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_CNH),'$#,##0.00'),
if(Currency='CAD', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_CAD),'$#,##0.00'),
if(Currency='CHF', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_CHF),'$#,##0.00'),
if(Currency='AUD', num(Sum({<ReportingMonthEnd={'$(=$(vEndDate))'}>}CURR_FUND_TNA_AUD),'$#,##0.00')

)))))))))

Thank you again for sharing the solution. 

[Lech_Miszkiewicz]

Ok, thats great - thanks for letting us know. 

It would be useful for benefit of others to actually see template layout (you could blur out sensitive content if there is any) as that approach can only work in certain scenarios.

cheers