Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
In a pivot table, I have the following expression which results in calculated values for "Tot" and "%NStd" screen pasted below.
=if(columnno()=0, num(round(sum(total <ASSIGNED_TEAM,UW,REPORTING_PERIOD> CNT)),'#,##0')
&' '&
num(round(sum({<REPORTING_PERIOD={'Non-Std'}>}CNT))/round(sum(total <ASSIGNED_TEAM,UW,REPORTING_PERIOD> CNT)),'#,##0%'),num(round(sum(CNT)),'#,##0'))
As you can see, the alignment for the values under "Tot" goes awry with the varying results under "%NStd".
What can I put in my expression so that the values under "Tot" are all always right aligned no matter the results under "%NStd"?
Thank you.
Try this now. Almost there it handles everything including 100% issue also
=if(columnno()=0,
(num(round(sum(total <TEAM,CustomerName,REPORTING_PERIOD> CNT)),'#,##0'))
&' '& if(round(sum({<REPORTING_PERIOD={'Non-Std'}>}CNT))/round(sum(total <TEAM,CustomerName,REPORTING_PERIOD> CNT))<0.1,num(round(sum({<REPORTING_PERIOD={'Non-Std'}>}CNT))/round(sum(total <TEAM,CustomerName,REPORTING_PERIOD> CNT)),'#,##0 %'),
if(round(sum({<REPORTING_PERIOD={'Non-Std'}>}CNT))/round(sum(total <TEAM,CustomerName,REPORTING_PERIOD> CNT))=1,num(round(sum({<REPORTING_PERIOD={'Non-Std'}>}CNT))/round(sum(total <TEAM,CustomerName,REPORTING_PERIOD> CNT)),'#,##0%'),
num(round(sum({<REPORTING_PERIOD={'Non-Std'}>}CNT))/round(sum(total <TEAM,CustomerName,REPORTING_PERIOD> CNT)),'#,##0 %')))
,num(round(sum(CNT)),'#,##0'))
Can you share a sample app to look into?
How do I insert a qvw file in this thread?
When you click on reply button you can see Use advanced editor on top right of the reply window, when you click on it, you can see a Attach link on bottom right.
Here is the sample. Thanks for your assistance.
The issue is with single digit %NStd values.So you can add additional zero for number format '#,##00%' instead of '#,##0%'.But 0% will be shown as 00%.See attached app.
Thanks for your effort Chai but does not work for 100% and with single percentage results such as 5% it looks like 05% which doesn't look quite right.
I think the best solution would be to separate the Volume Total with the %NStd total into two total columns but I cannot seem to figure out how to do that without messing up the whole pivot table.
I agree it is not perfect solution.
Try this expression.Not perfect but it helps .I see issue with 100% also
=if(columnno()=0,
num(round(sum(total <TEAM,CustomerName,REPORTING_PERIOD> CNT)),'#,##0')
&' '& if(round(sum({<REPORTING_PERIOD={'Non-Std'}>}CNT))/round(sum(total <TEAM,CustomerName,REPORTING_PERIOD> CNT))<0.1,num(round(sum({<REPORTING_PERIOD={'Non-Std'}>}CNT))/round(sum(total <TEAM,CustomerName,REPORTING_PERIOD> CNT)),'#,##0 %'),
num(round(sum({<REPORTING_PERIOD={'Non-Std'}>}CNT))/round(sum(total <TEAM,CustomerName,REPORTING_PERIOD> CNT)),'#,##0%'))
,num(round(sum(CNT)),'#,##0'))
Almost there except when the value = 10%, it looks off.