Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

To Show Percentage in Pivot Table

ItemJuly$July%Aug$Aug%
A20,00020%10,00020%
B40,00040%35,00070%
C40,00040%5,00010%

How to display July% and Aug % in Pivot table?

8 Replies
sunny_talwar

I am assuming you have two dimensions here, so try this to get percentages

Sum(Measure)/Sum(TOTAL <MonthField> Measure)

rubenmarin

Hi, there is a keyword TOTAL that is used to avoid chart dimensions. In example, to sum all the items:

Sum(TOTAL ValueField)

So, if for example your expression is:

Sum(Value)

The percentage can be:

Sum(Value)/Sum(TOTAL Value)

If you are using a vertical dimension for months, you can set in TOTAL wich fields to totalize, in example:

Sum(Value)/Sum(TOTAL <MonthField> Value) // Will get the percentage per month (MonthField dimension is not ignored)


Hope I have explained.

Not applicable
Author

I'm using "measures" instead of dimensions; both for July$ and July%.

The formula for July$ is

Sum({<[Doc FY]={'2017'}>}[Amount in local cur.])

where I'm filtering Document Financial Year 2017. The value is Amount in local currency.

I have put this formula for July % and its error? How to correct this ya? I'm really new to QlikSense.

Sum({<[Doc FY]={'2017'}>}[Amount in local cur.])/Sum(TOTAL <[Doc Mth]>({<[Doc FY]={'2017'}>}[Amount in local cur.]))

sunny_talwar

Try this:

Sum({<[Doc FY]={'2017'}>}[Amount in local cur.])/Sum(TOTAL {<[Doc FY]={'2017'}>} [Amount in local cur.])

Not applicable
Author

It works! Thanks... !

Sunny T wrote:

Try this:

Sum({<[Doc FY]={'2017'}>}[Amount in local cur.])/Sum(TOTAL {<[Doc FY]={'2017'}>} [Amount in local cur.])

Not applicable
Author

Now the requirement has changed. The above formula resulted as follow

Item  2017     Jul%           Aug%

          100%     20%             80%

A         25%     5%               20%

B         25%     5%              20%

C         50%      10%            40%

The gold is to have each column row total to be 100%. Can you please advise the formula?

sunny_talwar

Do you have two expressions and 1 dimension or 2 dimension and 1 expression? Can list all your dimensions and expressions here?

sanket94
Contributor III
Contributor III

Hi,

   I am trying to total up by the date and not the total of entire data .  For example attached :

Sum of Counts:5989 

If I use total formula it takes: 2277480

Percent shows up as 0.3%

 

But I want to use 

Sum of counts:5989

Total should be using: 71167

The correct percentage should be 8% 

.

Could someone help me with this? Thank you in advance.