11 Replies Latest reply: May 17, 2016 11:54 AM by Akpofure Enughwure

# % total in straight table

Hi I have a straight table as follows:

the % change since Feb will not show correctly.  The total mode is sum of rows but I have tried all the variations here and they won't work for me.  Any help would be appreciated

The expression for this is:

if(IsNull(Pick(Match(ValueList(\$(vStaffCategory)),\$(vStaffCategory)),

num(((Avg({\$<HRPrimaryCare={15},ReferredMM={\$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM))}>}ActualValue)-Avg({\$<HRPrimaryCare={15},ReferredMM={\$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue))/Avg({\$<HRPrimaryCare={15},ReferredMM={\$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue)), vNumber2),
num(((Avg({\$<HRPrimaryCare={16},ReferredMM={\$(#=Max({<[HRPrimaryCare]={16}>}ReferredMM))}>}ActualValue)-Avg({\$<HRPrimaryCare={16},ReferredMM={\$(#=Max({<[HRPrimaryCare]={16}>}ReferredMM)-1)}>}ActualValue))/Avg({\$<HRPrimaryCare={16},ReferredMM={\$(#=Max({<[HRPrimaryCare]={16}>}ReferredMM)-1)}>}ActualValue)), vNumber2),
num(((Avg({\$<HRPrimaryCare={17},ReferredMM={\$(#=Max({<[HRPrimaryCare]={17}>}ReferredMM))}>}ActualValue)-Avg({\$<HRPrimaryCare={17},ReferredMM={\$(#=Max({<[HRPrimaryCare]={17}>}ReferredMM)-1)}>}ActualValue))/Avg({\$<HRPrimaryCare={17},ReferredMM={\$(#=Max({<[HRPrimaryCare]={17}>}ReferredMM)-1)}>}ActualValue)), vNumber2),
num(((Avg({\$<HRPrimaryCare={18},ReferredMM={\$(#=Max({<[HRPrimaryCare]={18}>}ReferredMM))}>}ActualValue)-Avg({\$<HRPrimaryCare={18},ReferredMM={\$(#=Max({<[HRPrimaryCare]={18}>}ReferredMM)-1)}>}ActualValue))/Avg({\$<HRPrimaryCare={18},ReferredMM={\$(#=Max({<[HRPrimaryCare]={18}>}ReferredMM)-1)}>}ActualValue)), vNumber2),
num(((Avg({\$<HRPrimaryCare={19},ReferredMM={\$(#=Max({<[HRPrimaryCare]={19}>}ReferredMM))}>}ActualValue)-Avg({\$<HRPrimaryCare={19},ReferredMM={\$(#=Max({<[HRPrimaryCare]={19}>}ReferredMM)-1)}>}ActualValue))/Avg({\$<HRPrimaryCare={19},ReferredMM={\$(#=Max({<[HRPrimaryCare]={19}>}ReferredMM)-1)}>}ActualValue)), vNumber2),
num(((Avg({\$<HRPrimaryCare={20},ReferredMM={\$(#=Max({<[HRPrimaryCare]={20}>}ReferredMM))}>}ActualValue)-Avg({\$<HRPrimaryCare={20},ReferredMM={\$(#=Max({<[HRPrimaryCare]={20}>}ReferredMM)-1)}>}ActualValue))/Avg({\$<HRPrimaryCare={20},ReferredMM={\$(#=Max({<[HRPrimaryCare]={20}>}ReferredMM)-1)}>}ActualValue)), vNumber2))),Null(),

Pick(Match(ValueList(\$(vStaffCategory)),\$(vStaffCategory)),

num(((Sum({\$<HRPrimaryCare={15},ReferredMM={\$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM))}>}ActualValue)-Sum({\$<HRPrimaryCare={15},ReferredMM={\$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue))/Sum({\$<HRPrimaryCare={15},ReferredMM={\$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue)), vNumber2),
num(((Sum({\$<HRPrimaryCare={16},ReferredMM={\$(#=Max({<[HRPrimaryCare]={16}>}ReferredMM))}>}ActualValue)-Sum({\$<HRPrimaryCare={16},ReferredMM={\$(#=Max({<[HRPrimaryCare]={16}>}ReferredMM)-1)}>}ActualValue))/Sum({\$<HRPrimaryCare={16},ReferredMM={\$(#=Max({<[HRPrimaryCare]={16}>}ReferredMM)-1)}>}ActualValue)), vNumber2),
num(((Sum({\$<HRPrimaryCare={17},ReferredMM={\$(#=Max({<[HRPrimaryCare]={17}>}ReferredMM))}>}ActualValue)-Sum({\$<HRPrimaryCare={17},ReferredMM={\$(#=Max({<[HRPrimaryCare]={17}>}ReferredMM)-1)}>}ActualValue))/Sum({\$<HRPrimaryCare={17},ReferredMM={\$(#=Max({<[HRPrimaryCare]={17}>}ReferredMM)-1)}>}ActualValue)), vNumber2),
num(((Sum({\$<HRPrimaryCare={18},ReferredMM={\$(#=Max({<[HRPrimaryCare]={18}>}ReferredMM))}>}ActualValue)-Sum({\$<HRPrimaryCare={18},ReferredMM={\$(#=Max({<[HRPrimaryCare]={18}>}ReferredMM)-1)}>}ActualValue))/Sum({\$<HRPrimaryCare={18},ReferredMM={\$(#=Max({<[HRPrimaryCare]={18}>}ReferredMM)-1)}>}ActualValue)), vNumber2),
num(((Sum({\$<HRPrimaryCare={19},ReferredMM={\$(#=Max({<[HRPrimaryCare]={19}>}ReferredMM))}>}ActualValue)-Sum({\$<HRPrimaryCare={19},ReferredMM={\$(#=Max({<[HRPrimaryCare]={19}>}ReferredMM)-1)}>}ActualValue))/Sum({\$<HRPrimaryCare={19},ReferredMM={\$(#=Max({<[HRPrimaryCare]={19}>}ReferredMM)-1)}>}ActualValue)), vNumber2),
num(((Sum({\$<HRPrimaryCare={20},ReferredMM={\$(#=Max({<[HRPrimaryCare]={20}>}ReferredMM))}>}ActualValue)-Sum({\$<HRPrimaryCare={20},ReferredMM={\$(#=Max({<[HRPrimaryCare]={20}>}ReferredMM)-1)}>}ActualValue))/Sum({\$<HRPrimaryCare={20},ReferredMM={\$(#=Max({<[HRPrimaryCare]={20}>}ReferredMM)-1)}>}ActualValue)), vNumber2)))

• ###### Re: % total in straight table

Just looking at the expression won't be very helpful. Would you be able to share a sample and also point out what you expected vs. what you got?

• ###### Re: % total in straight table

the total of this column should be 0.28% I cannot send a sample sorry

• ###### Re: % total in straight table

Hello, How about u sending us the log file. That might help us crack this case.

• ###### Re: % total in straight table

Would you be able to share the two expressions that you are dividing to get the percentage calculated here?

• ###### Re: % total in straight table

Hi sunny

They are a bit complicated but below is the expression that will bring up the % change since feb for Medical/Dental which is correct.  it is when these correct % per division are totalled they are just totalled rather than showing the correct figure. not sure if this will make sense to you

num(((Sum({\$<HRPrimaryCare={15},ReferredMM={\$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM))}>}ActualValue)-Sum({\$<HRPrimaryCare={15},ReferredMM={\$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue))/Sum({\$<HRPrimaryCare={15},ReferredMM={\$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue)), vNumber2),

• ###### Re: % total in straight table

Can you try this:

(Sum(Aggr(Sum({\$<HRPrimaryCare={15},ReferredMM={\$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM))}>}ActualValue), DivisionFieldName))

-

Sum(Aggr(Sum({\$<HRPrimaryCare={15},ReferredMM={\$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue), DivisionFieldName)))

/

Sum(Aggr(Sum({\$<HRPrimaryCare={15},ReferredMM={\$(#=Max({<[HRPrimaryCare]={15}>}ReferredMM)-1)}>}ActualValue), DivisionFieldName))

Replace DivisionFieldName with you actual Division Field

• ###### Re: % total in straight table

Use TOTAL <GroupingDim1,GroupingDim2,GroupingDim3,...>

• ###### Re: % total in straight table

Hi,

have you tried using aggr in order to fix your totals issue?

Kind regards,

• ###### Re: % total in straight table

where do I use the aggr? thanks

• ###### Re: % total in straight table

I can't get any of your suggestions to work this time but I have changed it to a pivot table and just added in totals to my variable and expressions which brings up the correct total.

thank you for all your help as usual.

• ###### Re: % total in straight table

You are welcome, Keep sharing. Together we are stronger and better.