topic Re: Percentage calc in QlikView

Percentage calc

surajap123 — Tue, 02 Sep 2014 13:33:53 GMT

Hi Experts,

I want to get the Percentage of sales made by Dim1, Dim2 or Dim3.

I am not sure how to write the expression to achieve this.

I have straight table with below dimensions and expressions

Dimensions:

Dim1

cyclicgroup contain-Dim2 & Dim3

Expression:

sum(sales)

I have created the app with sample data.. please help..

Re: Percentage calc

JonnyPoole — Tue, 02 Sep 2014 13:36:58 GMT

sum(Sales) / sum( TOTAL Sales) ...will give you the % of total.

Try removing the 1st dimension and adding dim1 to the cycle. The new expresison will give you a % of total for any dim you select in the cycle group.

Re: Percentage calc

MK_QSL — Tue, 02 Sep 2014 13:38:36 GMT

Use two Expressions

1) sum(Sales)

2) sum(Sales) ..... Tick Relative option...in expression tab

Re: Percentage calc

rustyfishbones — Tue, 02 Sep 2014 13:41:31 GMT

Do as both guys said

Re: Percentage calc

surajap123 — Tue, 02 Sep 2014 13:52:13 GMT

Thanks for all the suggestions..

Dim1 is the main category, so i want it to be always present in the chart and want to switch between Dim2 and Dim3.

I have updated the data in the app. Please help me with the solution.

Re: Percentage calc

MK_QSL — Tue, 02 Sep 2014 13:55:39 GMT

Still you can use my solution....

Create Two Expressions

1) SUM(sales)

2) SUM(sales) ...Tick Relative option in Expression Tab

Re: Percentage calc

maleksafa — Tue, 02 Sep 2014 13:59:35 GMT

use the relative box under the expression tab, the easiest way for straight forward percentage calculation.

Re: Percentage calc

Tue, 02 Sep 2014 14:03:50 GMT

Suraj,

You should be able to do this with the calculation of:

(Sales) / sum(total Sales)

I attached a workbook for you. QlikView recalculates the sum(sales) when you change dimensions in the group, so, your % total will change accordingly.

Re: Re: Percentage calc

JonnyPoole — Tue, 02 Sep 2014 14:05:57 GMT

Would you like the Dim2 or Dim3 values to be a % of grand total or a %subtotal of Dim1 ?

Both options below. For the subtotal , use: sum( Sales) / sum(total <Dim1> Sales)

Re: Percentage calc

surajap123 — Tue, 02 Sep 2014 14:14:50 GMT

Thanks everyone for the great help