topic Limiting the dimension values in pivot table. in QlikView

Limiting the dimension values in pivot table.

Anonymous — Tue, 10 Dec 2013 18:18:16 GMT

Hi All,

I'm trying to create a pivot table chart in which the dimension is Region and their values are APAC,EMEA,NA and LATAM and I'm summing the sales value for these dimensions over another dimension which is year. Now only APAC is divided into APAC, APAC Direct and APAC Channel.

Now:

1. I'm trying to come up with single APAC value which should have the sum of sales for APAC + APAC Direct + APAC Channel

2. Is it possible to show only APAC value in pivot table but the final sum should show the sum for APAC Direct and APAC Channel also.

Please suggest!!!

-Abhishek

Re: Limiting the dimension values in pivot table.

vgutkovsky — Tue, 10 Dec 2013 18:28:33 GMT

Simplest way to do this would be to just create a higher-level field in the data model. Something like this:

if(substringcount(Region,'APAC')>0,'APAC',Region) as [Region High Level]

and then just use this new field in your pivot table instead of the current Region field.

Regards,

Vlad

Re: Limiting the dimension values in pivot table.

Anonymous — Wed, 11 Dec 2013 05:14:24 GMT

Dear Vlad,

Thanks for replying and for my query 1, it is working absolutely fine. But I'm also trying to figure out the answer for Query 2 where I just want to hide the APAC Direct and APAC channel vlaue in pivot and only show the APAC value but the final total for the pivot, APAC Direct and APAC channel will be included.

Appreciate if you can provide me a workaround for that.

Regards,

-Abhishek

Re: Limiting the dimension values in pivot table.

vgutkovsky — Thu, 12 Dec 2013 17:34:31 GMT

Sounds like you just need to add your original Region field as a dimension after the new Region High Level field. Then use the + sign in the pivot table to show totals at that level. Also, under Presentation, enabled Partial Sums for the original Region level.

Regards,

Vlad