topic How to do this in Pivot Table in QlikView

How to do this in Pivot Table

Anonymous — Mon, 19 Sep 2016 00:01:19 GMT

I have a dimension less pivot table and I want to get rid of the row as show below. is it possible. pls see qvw and excel file attached.

Many Thanks

Re: How to do this in Pivot Table

sunny_talwar — Mon, 19 Sep 2016 00:07:11 GMT

How about using a straight table?

Re: How to do this in Pivot Table

Anonymous — Mon, 19 Sep 2016 02:31:36 GMT

Hi Sunny, how were you able to list expressions at row level? when you add expns in a straight table, they get added to the right. that is why I chose pivot table. can you pls explain. Thanks

Re: How to do this in Pivot Table

sunny_talwar — Mon, 19 Sep 2016 02:44:38 GMT

‌On the Presentation tab of straight table, you will find the option to check 'Horizontal', this will change the view of your chart to how you see in the image above.

Best,

Sunny

Re: How to do this in Pivot Table

Anonymous — Mon, 19 Sep 2016 02:54:37 GMT

Thank you Sunny!!

Re: How to do this in Pivot Table

Anonymous — Tue, 20 Sep 2016 00:07:42 GMT

Hi Sunny, is it possible to have only 'Total Liability' (Expression Label) in red font? the expression itself can be done but can the label be done as well?

Re: How to do this in Pivot Table

sunny_talwar — Tue, 20 Sep 2016 00:18:27 GMT

May be using an alternative technique to do this then

Re: How to do this in Pivot Table

Anonymous — Tue, 20 Sep 2016 04:03:30 GMT

That was awesome. additionally, I needed metrics number to be red and for that I did as in the image.

so from the expression you used,

Pick(Dim1,

$(V_assets),

Num($(V_Equity_per), '##.00%'),

$(V_Liability),

$(V_Stock),

$(V_Expenses))

Liability gets the value Dim1 =3....? can you pls explain how this logic works. I know I am asking for a lot.

Re: How to do this in Pivot Table

Anonymous — Thu, 29 Sep 2016 00:02:38 GMT

Hi Sunny is it possible to flip the positions of the colmns. so can I have numbers in 1st coumn and text in 2nd?

Re: How to do this in Pivot Table

sunny_talwar — Thu, 29 Sep 2016 00:43:50 GMT

You sure can. Add a new expression Dim with the same settings that we applied to the actual dimension.

One the presentation tab, hide the actual dimension

Re: How to do this in Pivot Table

Anonymous — Thu, 29 Sep 2016 21:55:51 GMT

Thank you for your reply. trying to understand the logic... so to be able to flip the column in straight table, it has to be in Expression form?

Re: How to do this in Pivot Table

sunny_talwar — Thu, 29 Sep 2016 21:59:15 GMT

It is just the way it is structured that we need to create a new expression to get this done. But is this not what you wanted?

Re: How to do this in Pivot Table

Anonymous — Fri, 30 Sep 2016 04:32:06 GMT

yes this is what I want. Thank you.

Re: How to do this in Pivot Table

Anonymous — Fri, 02 Dec 2016 00:42:42 GMT

stalwar1‌

Hi Sunny I am struggling with a related problem to this thread.

I need to have conditional background-color formatting in 1st two expns:

1) For Expn Total Company Assets, I need the background color of numerical value to be Green RGB(74,159,62) if > 80

or else red RGB(255,0,0). so in this case 83 will have a green background

2) For Gain Percentage, I need the background color of numerical value to be Green RGB(74,159,62) if > 50%

or else red RGB(255,0,0). so in this case 39.76% will have a red background.

I tried playing with pick same logic you applied to calculate main expn but no luck. Thank you in advance

Re: How to do this in Pivot Table

sunny_talwar — Fri, 02 Dec 2016 10:14:19 GMT

Would you be able to share a sample?

Re: How to do this in Pivot Table

sunny_talwar — Fri, 02 Dec 2016 10:14:46 GMT

Or is the same sample which is attached is applicable here?

Re: How to do this in Pivot Table

Anonymous — Fri, 02 Dec 2016 16:51:29 GMT

Sunny,

its the same sample attached above.

Re: How to do this in Pivot Table

sunny_talwar — Fri, 02 Dec 2016 16:58:04 GMT

May be like this:

=If(Dim1 = 1, If($(V_assets) > 80, RGB(74,159,62), RGB(255,0,0)),

If(Dim1 = 2, If($(V_Equity_per) > 0.50, RGB(74,159,62), RGB(255,0,0))))

Re: How to do this in Pivot Table

Anonymous — Fri, 02 Dec 2016 17:08:15 GMT

Thankyou for quick reply. I will apply in my actual app and update.

Re: How to do this in Pivot Table

Anonymous — Fri, 02 Dec 2016 17:34:22 GMT

Sunny,

Thank you again. That was it. You killed it!