Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)))
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?
Use TOTAL <GroupingDim1,GroupingDim2,GroupingDim3,...>
the total of this column should be 0.28% I cannot send a sample sorry
Hello, How about u sending us the log file. That might help us crack this case.
Hi,
have you tried using aggr in order to fix your totals issue?
Kind regards,
Would you be able to share the two expressions that you are dividing to get the percentage calculated here?
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),
where do I use the aggr? thanks
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