topic Re: show totals but suppress values in pivot table in QlikView

show totals but suppress values in pivot table

Mon, 16 May 2016 18:23:20 GMT

I have a pivot table as shown below. What I need is to suppress the YTD column and just show the total.

Re: show totals but suppress values in pivot table

sunny_talwar — Mon, 16 May 2016 19:20:26 GMT

Would you be able to share your qvw?

Re: show totals but suppress values in pivot table

Mon, 16 May 2016 19:23:44 GMT

Sorry, I could not as the data is Sensitive.

It's pretty Straight forward, two dimensions Year and month

Service % is the measure calculated by the formula Supply/Demand per month

YTD is just an extension of that.

Re: show totals but suppress values in pivot table

Clever_Anjos — Mon, 16 May 2016 20:05:39 GMT

I don´t think you can have 1 column at detail and 2 columns at total

Re: show totals but suppress values in pivot table

sunny_talwar — Mon, 16 May 2016 20:08:26 GMT

I think we might be able to use Synthetic dimensions or Island table dimension to do this. I was just hoping to see how the data is structured to give any suggestions.

Re: show totals but suppress values in pivot table

sunny_talwar — Mon, 16 May 2016 20:14:37 GMT

You can always scramble your data as mentioned here: Preparing examples for Upload - Reduction and Data Scrambling‌

Anyways, I will create a sample for you and you can check if this works for you or not

Re: show totals but suppress values in pivot table

sunny_talwar — Mon, 16 May 2016 20:34:39 GMT

Have a look here:

Re: show totals but suppress values in pivot table

Clever_Anjos — Mon, 16 May 2016 20:39:21 GMT

Very tricky and smart, congrats

Re: show totals but suppress values in pivot table

sunny_talwar — Mon, 16 May 2016 20:49:52 GMT

Thanks Clever. I think the dimension and expressions are still not perfect because I am not seeing the accumulation, but it can be further fine-tuned to get the desired result

Re: show totals but suppress values in pivot table

sunny_talwar — Mon, 16 May 2016 20:54:45 GMT

This is slightly better now

Re: show totals but suppress values in pivot table

rwunderlich — Mon, 16 May 2016 20:56:42 GMT

The way to accomplish this is define the YTD expresion as a calculated Dimension instead of an Expression.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: show totals but suppress values in pivot table

trdandamudi — Mon, 16 May 2016 21:00:54 GMT

Sunny,

Brilliant...you always find a way to resolve the issue...

Re: show totals but suppress values in pivot table

Tue, 17 May 2016 11:36:26 GMT

Thanks a lot Sunny. But i think i was not clear on my requirement, my apologies.

What i am trying to do here is suppress the YTD column and just show the total for YTD column, whereas the service% stays the same.

Re: show totals but suppress values in pivot table

Anonymous — Sat, 10 Sep 2016 10:25:27 GMT

sunny,

why we used total in the expression

i.e 1)before(total sum(sales)

2)column(total)

can you explain a little detail.

Re: show totals but suppress values in pivot table

Jbekhazi — Tue, 16 Jul 2019 12:58:21 GMT

Has anyone found a solution for this yet? I am facing the same thing currently.

Any assistance would be greatly appreciated.