topic How do I link from a pivot table cell to a new sheet ? in QlikView

How do I link from a pivot table cell to a new sheet ?

gerrycastellino — Wed, 26 Nov 2014 01:00:22 GMT

How do I link from a pivot table cell to a new sheet ?

Re: How do I link from a pivot table cell to a new sheet ?

JonnyPoole — Wed, 26 Nov 2014 02:41:37 GMT

I created a sample for this...

its not perfect . let me explain...

referring to the screenshot, i have 2 dimensions.. Year and Region.

There is a variable in the document with the following definition

=if(GetSelectedCount(Year)=1 and GetSelectedCount(REGION)=1,1,0)

If a user selects one year and one region, then the variables value changes to '1'.

Then i added an 'on change' trigger for the same variable to activate a sheet. The sheetID value is an expression where SH01 is the current sheet and SH02 is the next.

=if(vDrill=1,'SH02','SH01')

So...not perfect because if a user selects just one year and one region (through any other means) it will ALSO cause the sheet to move.

It could work but keep that in mind if you use this when you design the dashboard for the users.

Re: How do I link from a pivot table cell to a new sheet ?

gerrycastellino — Wed, 26 Nov 2014 19:44:35 GMT

jonathan:

I responded to the wrong thread, QV seems to be crashing for me when I add the variable and the link to the new sheet, see image below I get when I click on a cell in the pivot.

Gerry

Re: How do I link from a pivot table cell to a new sheet ?

JonnyPoole — Wed, 26 Nov 2014 20:06:41 GMT

What version are you using ( i was using 11.2 SR8) ? can you share the app with a small sample set ? if not, i'd like to see the exact expressions used everywhere.

Re: Re: How do I link from a pivot table cell to a new sheet ?

gerrycastellino — Wed, 26 Nov 2014 20:25:01 GMT

see attached with a sample data set.

The 2 sheets in play are the 2 on the RHS (Hierarchy, Hierarchy_Discovery)

in the variable event triggers section: I have the foll for v_hierarchy_drill:

=if(v_hierarchy_drill=1,'SH_Hierarchy','SH_Hierarchy_Discovery')

I'm just testing for FRY14A (REP_SUB_BS_ITEM) for now, which is why I've got my variable def as:

=if(GetSelectedCount(PERIOD_NAME_LONG)=1 and GetSelectedCount(REP_SUB_BS_ITEM)=1,1,0)

Re: Re: How do I link from a pivot table cell to a new sheet ?

gerrycastellino — Wed, 26 Nov 2014 20:41:06 GMT

using version: 11.20.12235.0

Re: Re: Re: How do I link from a pivot table cell to a new sheet ?

JonnyPoole — Wed, 26 Nov 2014 21:35:11 GMT

It definitely seems a little tempermental in this document. I had it working after renaming the sheets but it since stopped. Don't know the rhyme or reason.

I'll give it another shot later.