0
I have a pivot table where there are multiple column labels:
group
subgroup
subsubgroup
status
The last label, status
, has only two possible values, ACTIVE and DORMANT. I need to show only those rows where the value for ACTIVE is missing, but the value for DORMANT is not.
I know this is possible by partially aggregating the data (so my source data won't have a single COUNT field, but will have two: ACTIVE_COUNT and DORMANT_COUNT) and prefiltering it before constructing the pivot, but I cannot modify the shape of the source data for the pivot table in this particular case.
An example of what I need:
ROW G1 G2 G3 STAT CNT
---------------------
V11 AA BB C1 ACTV 100
V11 AA BB C1 DORM 200
V11 AA BB C2 ACTV 400
V11 AA BB C3 DORM 800
V11 AA BB C4 DORM 900
V21 AA BB C1 ACTV 110
V21 AA BB C1 DORM 220
V21 AA BB C2 ACTV 440
V21 AA BB C3 DORM 880
V21 AA BB C3 DORM 990
V21 AA BB C4 ACTV 1
Has to be transformed into:
. AA
. BB
. C3 C4
. DORM DORM
-------------
V11 800 900
V21 990
I.e., C1 is not shown because there's a matching row with ACTV>0, C2 is not shown because there's no DORM, V21 x C4 DORM is not shown because there's a matching row with ACTV>0, but both V11 x C4 DORM and V21 x C3 DORM are.
It will be easier for others to understand the problem if you can post sample data rather than simply explain it as text. – jjk_charles – 2015-01-21T16:29:09.583
Have you tried adding
Status
as a Report Filter? – Excellll – 2015-01-21T16:55:19.053@jjk_charles I added some sample data. – Alexey – 2015-01-22T07:25:33.707