Pivot table selection issue

[#1]

I have pivot table like below with 3 measure and one dimension

Measure	Dim	D1	D11	D12	D2	D21	D22
M1		42	39	24	44	47	48
M2		21	47	39	16	17	30
M3		19	31	21	33	29	26

Now requirement is like,

when I select the D1, I just want to see D11,D12

when I select the D2, I just want to see D21,D22

Bydefault, I just want to see D1, D2

[#2]

let me clear

My Data set is like below

Dim M1 M2 M3

D1   20  30  40

D11  21  23  45

D12  30  12  23

..

..

Table which I have shown in OP is Pivot table I have created on front end

[mightyqlikers]

create another field in script like below

if(Match(DIM,'D11','D12'),'D1',

  if(Match(DIM,'D21','D22'),'D2')) as NEW_DIM

$@M.

[Kushal_Chawda · Accepted solution]

I think it will be very difficult to achieve this by doing selection from dimension itself as when you select any dimension value rest all value will be in excluded state. Try like below

f1:
load *,if(Match(Dim,'D1','D11','D12'),'D1',
if(Match(Dim,'D2','D21','D22'),'D2'))as NewDim  inline [
Dim, M1,M2,M3
D1, 10,12,13
D11,13,10,34
D12,20,30,40
D2,10,34,5
D21,20,150,10
D22,23,45,67 ];

Create Pivot

Dimension:

Dim

Expressions:

M1-

=if(GetSelectedCount(NewDim)=0, sum({<Dim={'D1','D2'}>}M1),sum(M1))

M2-

=if(GetSelectedCount(NewDim)=0, sum({<Dim={'D1','D2'}>}M2),sum(M2))

M3-

=if(GetSelectedCount(NewDim)=0, sum({<Dim={'D1','D2'}>}M3),sum(M3))

[Anil_Babu_Samineni]

Try this

PFA. Follow same steps might be this is Useful

[sasikanth]

hi,

try this script

Temp:

LOAD DIm,

    left(DIm,2)  as Main,

    If(len(DIm)>2,DIm) as main,

    M1,

    M2,

    M3

FROM

[..\Downloads_Web\time.xlsx]

(ooxml, embedded labels, table is Sheet3);

in Pivot table tabke the two dims and enable based on condition

please refer the attachment

[susovan]

Hi,

Check this attachment,

Its might be work.

[Digvijay_Singh]

Hi Kush,

I think(couldn't prove it yet 100% ) that it is possible to be done simply in front end if we take list-box in alternate state, I tried through attached sample but it looks like some QV bug as it is not showing result properly for 'D2' as per below calculated dim.

=if(GetFieldSelections(Dim,'','','Group1')='D1',Aggr(only({1<Dim={'D11','D12'}>}Dim),Dim),

if(GetFieldSelections(Dim,'','','Group1')='D2',Aggr(only({1<Dim={'D11','D12'}>}Dim),Dim)))

[Digvijay_Singh]

Cal dimension needs correction -

=if(GetFieldSelections(Dim,'','','Group1')='D1',Aggr(only({1<Dim={'D11','D12'}>}Dim),Dim),

if(GetFieldSelections(Dim,'','','Group1')='D2',Aggr(only({1<Dim={'D21','D22'}>}Dim),Dim)))

Surprisingly when Qlikview is closed and reopened then the effect of change in D1 or D2 can be seen , looks like some deep concept involved here or a bug.

[Kushal_Chawda]

may be for D2 you need

if(GetFieldSelections(Dim,'','','Group1')='D2',Aggr(only({1<Dim={'D21','D22'}>}Dim),Dim)

[Kushal_Chawda]

We need stalwar1‌ here