Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Alignment in Expression

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

1 Solution

Accepted Solutions
chaper
Creator III
Creator III

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'))

Capture.PNG

View solution in original post

12 Replies
vishsaggi
Champion III
Champion III

Can you share a sample app to look into?

Not applicable
Author

How do I insert a qvw file in this thread?

vishsaggi
Champion III
Champion III

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.

Not applicable
Author

Here is the sample.  Thanks for your assistance.

chaper
Creator III
Creator III

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.

Capture.PNG

Not applicable
Author

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.

chaper
Creator III
Creator III

I agree it is not perfect solution.

chaper
Creator III
Creator III

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'))

Not applicable
Author

Almost there except when the value = 10%, it looks off.