topic Re: Average in pivot table in QlikView

Average in pivot table

rajaqliks — Mon, 14 Jan 2019 12:17:32 GMT

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:

Re: Average in pivot table

sunny_talwar — Mon, 14 Jan 2019 13:08:29 GMT

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

Re: Average in pivot table

sunny_talwar — Mon, 14 Jan 2019 13:28:04 GMT

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

Re: Average in pivot table

rajaqliks — Mon, 28 Jan 2019 14:32:30 GMT

Thanks sunny you are amazing.