Solved: Pivot table selection issue - Qlik Community

Report Inappropriate Content · ‎2016-06-17

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

Kushal_Chawda · ‎2016-06-17

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

View solution in original post

mightyqlikers · ‎2016-06-17

hi,

FYI.

$@M.

Digvijay_Singh · ‎2016-06-17

Can you show sample output, Do you mean you have single dimension with values D1,D11,D12,D2,D21,D22?

How do you want to show Measure M1,M2,M3? and Values below Dim. Normally measure means aggregation formula, do you have aggregation like Sum,Avg for M1,M2,M3? More info will help.

sujeetsingh · ‎2016-06-17

You can use nested expressions and even variables. Then use them in conditional show of an expression .

Report Inappropriate Content · ‎2016-06-17

Hi,

M1,M2,M3 having formula in pivot. and yes d1,d11... all are dimension values.. I have drag and drop the expression to come out with below format.

Report Inappropriate Content · ‎2016-06-17

Sam,

I am using PE. Can you please show all the steps?

Report Inappropriate Content · ‎2016-06-17

gwassenaar‌ swuehl‌ stalwar1‌ kush141087‌

mightyqlikers · ‎2016-06-17

FYI.,

Please find the attached.

$@M.

Report Inappropriate Content · ‎2016-06-17

I am not able to open. Can you please show the steps with screenshots?

mightyqlikers · ‎2016-06-17

DATA_TEMP:

CrossTable(DIM, VALUE)

LOAD Measure,

D1,

D11,

D12,

D2,

D21,

D22

FROM

datata.xlsx

(ooxml, embedded labels, table is Sheet1);

DATA:

LOAD

*,

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

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

Resident DATA_TEMP;

DROP Table DATA_TEMP;

and

take a pivot table with dimension DIM & Measure.

expression sum(value)

take list box NEW_DIM,

and values from NEW_DIM.

$@M.

Pivot table selection issue

Related Topics