Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

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

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- How to show percentage of subtotal in pivot table?

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

3,279 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)))

3,065 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

3,065 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)))

3,066 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

3,065 Views