topic Re: Drill-down from a Pivot Table in QlikView

Drill-down from a Pivot Table

Qrishna — Fri, 21 Aug 2015 19:57:10 GMT

Hi Qlikers,

Is there any way in a pivot table where I can drill down to the data from each expression column i.e the pivot table should act as if each of its field value is a drill down link to its related data.

Example:

When i click '128' i need to see 128 requests/details in some other table for period 2015-Aug.(i was able to achieve this).

Again when i click on 123, it deselects the period and gives me all the shipped/details from all the periods instead of giving me the shipped/details from the period 2015-Aug.

I've searched a lot in the community but couldn't find a proper solution for this.

Hoping for a solution.

I appreciate your time.

Thanks

Re: Drill-down from a Pivot Table

ecolomer — Sat, 22 Aug 2015 05:34:46 GMT

a possible solution to your requirement, would be overlapped to form each value a transparent button that you can program various actions such as deselect values, send to another tab, etc.

I do not know if I explained well?

Re: Drill-down from a Pivot Table

ecolomer — Sat, 22 Aug 2015 05:38:57 GMT

Here you have a possible example:

When you select an Product, show new tables

Re: Drill-down from a Pivot Table

Qrishna — Mon, 24 Aug 2015 15:48:59 GMT

Hi Enrique,

Thanks for the response.

I created a pivot table With expression Sum(bla bla) and 2 dimensions Period and Metrics.

When i click on any box(field value) the pivot table should be static(achieved with Sum({1} expression)) and i need to see the details(i got this too).

What i am looking for is, as soon as i click the same value in the same row or same column,the values gets deselected but each field value should act as a link to its details.

See attached.I have added some comments.

Re: Drill-down from a Pivot Table

Qrishna — Thu, 27 Aug 2015 17:49:27 GMT

Any Help Is Highly Appreciated.

gwassenaar‌ @Bill Britt swuehl‌ hic‌ jagan‌ maxgro‌ rwunderlich‌

or any one who can help.please do so.

Thanks in advance.

Re: Drill-down from a Pivot Table

sudeepkm — Thu, 27 Aug 2015 18:36:19 GMT

Selections are only applicable on dimensions. So whenever you try to click on a cell value it actually selects the dimension field values and shows that result.

you can have a Table box with product details such as Prod ID, Desc, Date etc. and conditionally show it whenever a selection is made on the Pivot table.

Your question: "Again when i click on 123, it deselects the period and gives me all the shipped/details from all the periods instead of giving me the shipped/details from the period 2015-Aug."

The number of records will no more be 123 if you want to deselect any value that is selected from the field Period.

In that case you can ask the end user to just select the Field "Status" to see all those products from all periods and only a particular Status (example Requests, Shipped etc.)

Re: Drill-down from a Pivot Table

Qrishna — Thu, 27 Aug 2015 18:52:31 GMT

Hi Sudeep,

below picture is a pivot table from a non-qlikview application.

here what happens is, when we click on any cell, it redirects to the data/transactions related to that selections

eg: click on 1388 provides us the details of all requests in 2015-aug (below the grid).

on immediate next click on 1248 provides details of all shipped in 2015-aug (below the grid).

so my requirement in qlikview is to design the same way.

So i designed a similar grid using pivot chart in qlikview but the functionality is differing.(dont worry about the numbers,they have been cut down here for demo purposes)

When i click '128' i am seeing 128 requests/details in some other table for period 2015-Aug.(i was able to achieve this).

Again on an immediate click on 123, it deselects/clears the period and gives me all the shipped/details from all the periods instead of giving me the shipped/details from the period 2015-Aug.

i dont want this to happen.

any suggestions?

Re: Drill-down from a Pivot Table

sudeepkm — Thu, 27 Aug 2015 19:36:07 GMT

EDIT: The problem will not occur in a Straight table but the feel of Pivot table will be gone which may not be acceptable.

Now I understand your problem. I'll need to think about it if this can be fixed. But I can tell why it is doing it like that.

First you clicked on 128 (Requests and Period = 2015 Aug) then you clicked on 123 (Shipped and Period = 2015 Aug)

So basically it is like, you selected 2015 Aug in your first selection and again you clicked on that and as a result QlikView clears that field values.

You can create a list box for a field and then select a value it will turn green and then again you click on that same value now it will show "all" values. Thats the reason it is clearing because the value remains same in the subsequent selection of the same field.

If you select a different combination of values then you can find that both the Fields will show values as selected. for example first you click on 128 then click on 306.

Re: Drill-down from a Pivot Table

Qrishna — Thu, 27 Aug 2015 20:29:45 GMT

Yes Exactly.

I know the dimensions in the pivot table were acting like 2 different list boxes. Thats the functionality of Qlikview Pivot table. I want to get rid of that clearing thing and make it behave as the non-qlikview application grid.

and yes selecting a different combination of values we can find that both the Fields will show values as selected.

but its not the case with values in the same row or same column.

i want to modify this functionality.

So i am looking some thing like macros or i dont knowwwwwwww what..

Re: Drill-down from a Pivot Table

jagan — Fri, 28 Aug 2015 06:04:12 GMT

Hi,

Please find attached file for solution. Hope it helps you.

Regards,

Jagan.

Re: Drill-down from a Pivot Table

Qrishna — Fri, 28 Aug 2015 14:52:33 GMT

Hi Jagan,

Thanks a lot for the effort and the reply.

I have made some comments in your qvw for what the current scenario is and what i actually need.

Please see attached.

Re: Drill-down from a Pivot Table

Qrishna — Tue, 01 Sep 2015 15:13:57 GMT

Hello everyone,

Finally i found the solution with help of Qlik Support.

They have provided some triggers and with those triggers i generated an isolated table with the aggregated data and used that in the pivot table hence making the pivot static drill down.

See attached.

Thanks everyone for your responses.

Happy Qliking.