Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
abhi251983
New Contributor II

Limiting the dimension values in pivot table.

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

3 Replies
vgutkovsky
Honored Contributor II

Re: Limiting the dimension values in pivot table.

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

abhi251983
New Contributor II

Re: Limiting the dimension values in pivot table.

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

vgutkovsky
Honored Contributor II

Re: Limiting the dimension values in pivot table.

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

Community Browser