topic Re: % total in straight table in QlikView

% total in straight table

rcorcoran — Tue, 17 May 2016 09:03:45 GMT

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

sunny_talwar — Tue, 17 May 2016 09:08:02 GMT

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

Anonymous — Tue, 17 May 2016 12:35:56 GMT

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

Re: % total in straight table

rcorcoran — Tue, 17 May 2016 12:41:16 GMT

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

Re: % total in straight table

akpofureenughwu — Tue, 17 May 2016 12:45:53 GMT

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

Re: % total in straight table

santiago_respane — Tue, 17 May 2016 12:47:15 GMT

Hi,

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

Kind regards,

Re: % total in straight table

sunny_talwar — Tue, 17 May 2016 12:51:57 GMT

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

Re: % total in straight table

rcorcoran — Tue, 17 May 2016 12:55:37 GMT

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

rcorcoran — Tue, 17 May 2016 12:56:10 GMT

where do I use the aggr? thanks

Re: % total in straight table

sunny_talwar — Tue, 17 May 2016 13:00:55 GMT

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

rcorcoran — Tue, 17 May 2016 15:38:03 GMT

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

akpofureenughwu — Tue, 17 May 2016 15:54:10 GMT

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