Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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?

7 Replies

Re: To Show Percentage in Pivot Table

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

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

Re: To Show Percentage in Pivot Table

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

Re: To Show Percentage in Pivot Table

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.]))

Re: To Show Percentage in Pivot Table

Try this:

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

Not applicable

Re: To Show Percentage in Pivot Table

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

Re: To Show Percentage in Pivot Table

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?

Re: To Show Percentage in Pivot Table

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