Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

You can succeed best and quickest by helping others to succeed. Join the conversation.

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: How to show percentage of subtotal in pivot ta...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

vjoshi2017

Contributor

2018-04-10
10:35 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How to show percentage of subtotal in pivot table?

I am trying to show percentage in pivot table in Qlik sense.

Quantity credit is how many numbers of a particular PNO are returned. For example, out of total sales for PNO 100330140, the last one, 124 are returned for various reasons.

the percentage returned is 9.68%, that means 1281 parts were sold before taking credits into account.

The next level is reason which tells us out of 9.68% how many were returned for different reasons.

This is what i wanted without selecting a part number, but on the pivot table itself. But when I expand pivot table for rows, I don't get desired results. I am getting percentage for whole data. Here I need 91.94%, 1.61%, and 6.45% for reasons C,F, and I respectively.

What I am getting is below :

Formulae I am using for second measure (%Quantity Credited) is :

**if(Dimensionality() <> 2, **

**fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/sum({<ORDTYPE={"SL"}>}TotalOrderQuantity), **

**fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/fabs(Sum(total{<ORDTYPE={"CR"}>}TotalOrderQuantity)))**

Please let me know how to get desired result.

Thanks,

Vivek

- Tags:
- pivot table

2,842 Views

1 Solution

Accepted Solutions

boorgura

Specialist

2018-04-10
11:06 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

It should be

if(Dimensionality() <> 2,

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/sum({<ORDTYPE={"SL"}>}TotalOrderQuantity),

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/fabs(Sum(total **<****PNO>** {<ORDTYPE={"CR"}}TotalOrderQuantity)))

2,628 Views

4 Replies

boorgura

Specialist

2018-04-10
10:47 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You are using TOTAL which will ignore all dimensions, but you should ignore the first dimension:

**if(Dimensionality() <> 2,**

**fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/sum({<ORDTYPE={"SL"}>}TotalOrderQuantity), **

**fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/fabs(Sum(total < first_Dim> {<ORDTYPE={"CR"}>}TotalOrderQuantity)))**

vjoshi2017

Contributor

2018-04-10
10:59 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I used the following expression as well :

if(Dimensionality() <> 2,

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/sum({<ORDTYPE={"SL"}>}TotalOrderQuantity),

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/fabs(Sum(total PNO{<ORDTYPE={"CR"}}TotalOrderQuantity)))

It doesn't work.

The thing is this work when I am selecting a part number. the percentages are correct. I believe there is an issue in aggregate because after selecting one part number, aggregate would be same.

Thanks,

Vivek

2,628 Views

boorgura

Specialist

2018-04-10
11:06 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

It should be

if(Dimensionality() <> 2,

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/sum({<ORDTYPE={"SL"}>}TotalOrderQuantity),

fabs(Sum({<ORDTYPE={"CR"}>}TotalOrderQuantity))/fabs(Sum(total **<****PNO>** {<ORDTYPE={"CR"}}TotalOrderQuantity)))

2,629 Views

vjoshi2017

Contributor

2018-04-10
11:08 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks Rakesh

2,628 Views