Average in pivot table

[rajaqliks]

Hi experts,

I need to show total average and total in pivot table, i got totals but i am not able to get average please help,

 i am adding qvw as well as formula too

@Path  : on application second tab means Coverage tab bottom pivot table.

formula: 

if(GetFieldSelections(MDESC)='TargetOnly',sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"},RightfulOwner={'Y'},TargetFlag={'Y'}>}IUPS),
if(GetFieldSelections(MDESC)='Territory Only',sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"},RightfulOwner={'Y'}>}IUPS),
if(GetFieldSelections(MDESC)='Full Universe',sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"}>}IUPS))))/

sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"}>}IUPS)

target:

[sunny_talwar]

You are looking to get another row where you can show the average of IHS, VA, and DoD for each WeekEnd Date?

[sunny_talwar · Accepted solution]

Are you looking for something like this?

Added a new table in the script

Dim:
LOAD * INLINE [
    Dim
    1
    2
    3
];

and then changed your 1st dimension to this

=Pick(Dim, Group, 'Total', 'Average')

Expression

Pick(Dim,

Num(if(GetFieldSelections(MDESC)='TargetOnly',sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"},RightfulOwner={'Y'},TargetFlag={'Y'}>}IUPS),
if(GetFieldSelections(MDESC)='Territory Only',sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"},RightfulOwner={'Y'}>}IUPS),
if(GetFieldSelections(MDESC)='Full Universe',sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"}>}IUPS)))), '#,###.'),

Num(if(GetFieldSelections(MDESC)='TargetOnly',sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"},RightfulOwner={'Y'},TargetFlag={'Y'}>}IUPS),
if(GetFieldSelections(MDESC)='Territory Only',sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"},RightfulOwner={'Y'}>}IUPS),
if(GetFieldSelections(MDESC)='Full Universe',sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"}>}IUPS)))), '#,###.'),

Num((if(GetFieldSelections(MDESC)='TargetOnly',sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"},RightfulOwner={'Y'},TargetFlag={'Y'}>}IUPS),
if(GetFieldSelections(MDESC)='Territory Only',sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"},RightfulOwner={'Y'}>}IUPS),
if(GetFieldSelections(MDESC)='Full Universe',sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"}>}IUPS))))/

sum({<WEEKEND = {"$(='>=' & Date(Max(WEEKEND) - 77, 'MM/DD/YYYY') & '<=' & Date(Max(WEEKEND), 'DD/MM/YYYY'))"}>}IUPS)), '#0.0%'))

and Background color for the 1st dimension and expression

=If(Dim >= 2, RGB(225, 225, 225))

and Text Format for the 1st dimension and expression

=If(Dim >= 2, '<B>')

[rajaqliks]

Thanks sunny you are amazing.