topic Re: Pivot table selection issue in QlikView

Pivot table selection issue

Fri, 17 Jun 2016 06:36:08 GMT

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

Re: Pivot table selection issue

mightyqlikers — Fri, 17 Jun 2016 06:46:21 GMT

hi,

FYI.

$@M.

Re: Pivot table selection issue

Digvijay_Singh — Fri, 17 Jun 2016 06:46:52 GMT

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.

Re: Pivot table selection issue

sujeetsingh — Fri, 17 Jun 2016 06:56:49 GMT

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

Re: Pivot table selection issue

Fri, 17 Jun 2016 06:57:40 GMT

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.

Re: Pivot table selection issue

Fri, 17 Jun 2016 06:58:39 GMT

Sam,

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

Re: Pivot table selection issue

Fri, 17 Jun 2016 07:00:14 GMT

gwassenaar‌ swuehl‌ stalwar1‌ kush141087‌

Re: Pivot table selection issue

mightyqlikers — Fri, 17 Jun 2016 07:02:45 GMT

FYI.,

Please find the attached.

$@M.

Re: Pivot table selection issue

Fri, 17 Jun 2016 07:04:24 GMT

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

Re: Pivot table selection issue

mightyqlikers — Fri, 17 Jun 2016 07:08:04 GMT

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.

Re: Pivot table selection issue

Fri, 17 Jun 2016 07:12:32 GMT

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

Re: Pivot table selection issue

mightyqlikers — Fri, 17 Jun 2016 07:14:52 GMT

create another field in script like below

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

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

$@M.

Re: Pivot table selection issue

Kushal_Chawda — Fri, 17 Jun 2016 07:47:18 GMT

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

Re: Pivot table selection issue

Anil_Babu_Samineni — Fri, 17 Jun 2016 08:07:23 GMT

Try this

PFA. Follow same steps might be this is Useful

Re: Pivot table selection issue

sasikanth — Fri, 17 Jun 2016 08:13:00 GMT

hi,

try this script

Temp:

LOAD DIm,

left(DIm,2) as Main,

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

M1,

M2,

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

Re: Pivot table selection issue

susovan — Fri, 17 Jun 2016 09:58:03 GMT

Hi,

Check this attachment,

Its might be work.

Re: Pivot table selection issue

Digvijay_Singh — Fri, 17 Jun 2016 10:04:46 GMT

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

Re: Pivot table selection issue

Digvijay_Singh — Fri, 17 Jun 2016 10:16:35 GMT

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.

Re: Pivot table selection issue

Kushal_Chawda — Fri, 17 Jun 2016 10:20:47 GMT

may be for D2 you need

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

Re: Pivot table selection issue

Kushal_Chawda — Fri, 17 Jun 2016 10:21:53 GMT

We need stalwar1‌ here