
Re: To Show Percentage in Pivot Table
Sunny Talwar Dec 21, 2016 9:58 AM (in response to San Li Ho)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
San Li Ho Dec 21, 2016 7:58 PM (in response to Sunny Talwar )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
Sunny Talwar Dec 21, 2016 8:47 PM (in response to San Li Ho)Try this:
Sum({<[Doc FY]={'2017'}>}[Amount in local cur.])/Sum(TOTAL {<[Doc FY]={'2017'}>} [Amount in local cur.])

Re: To Show Percentage in Pivot Table
San Li Ho Dec 21, 2016 8:55 PM (in response to Sunny Talwar )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.])

Re: To Show Percentage in Pivot Table
San Li Ho Dec 30, 2016 2:48 AM (in response to Sunny Talwar )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
Sunny Talwar Dec 30, 2016 5:24 AM (in response to San Li Ho)Do you have two expressions and 1 dimension or 2 dimension and 1 expression? Can list all your dimensions and expressions here?





Re: To Show Percentage in Pivot Table
Ruben Marin Dec 21, 2016 10:03 AM (in response to San Li Ho)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.