topic Re: Alignment in Expression in QlikView

Alignment in Expression

Mon, 05 Jun 2017 16:19:10 GMT

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.

Re: Alignment in Expression

vishsaggi — Mon, 05 Jun 2017 16:28:31 GMT

Can you share a sample app to look into?

Re: Alignment in Expression

Mon, 05 Jun 2017 16:50:32 GMT

How do I insert a qvw file in this thread?

Re: Alignment in Expression

vishsaggi — Mon, 05 Jun 2017 16:59:50 GMT

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.

Re: Alignment in Expression

Mon, 05 Jun 2017 17:59:06 GMT

Here is the sample. Thanks for your assistance.

Re: Alignment in Expression

chaper — Mon, 05 Jun 2017 18:48:13 GMT

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.

Re: Alignment in Expression

Mon, 05 Jun 2017 19:00:38 GMT

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.

Re: Alignment in Expression

chaper — Mon, 05 Jun 2017 19:18:39 GMT

I agree it is not perfect solution.

Re: Alignment in Expression

chaper — Mon, 05 Jun 2017 19:43:09 GMT

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

Re: Alignment in Expression

Mon, 05 Jun 2017 20:05:07 GMT

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

Re: Alignment in Expression

chaper — Mon, 05 Jun 2017 20:10:27 GMT

I think 10% looks fine except 100% right ?

Re: Alignment in Expression

Mon, 05 Jun 2017 20:18:01 GMT

Some of the 10% looks good and some don't depending on the rounding.

In any case, I think I will close this out and see if I ass for assistance to split the two columns. The formatting thing doesn't seem to be working out as the best solution.

Re: Alignment in Expression

chaper — Mon, 05 Jun 2017 20:37:04 GMT

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