topic Re: Aggregating the Values Dimension Totals Wise in QlikView

Aggregating the Values Dimension Totals Wise

umartareen — Mon, 02 May 2016 07:51:41 GMT

Hi,

How can we calculate the Values using the totals of second dimensions :

Example :

The expression columns in the above Pivot are

Value = Sum(Value)

Sum TOTAL Value = Sum(Value) / Sum(TOTAL Value)

The above "Sum Total Value" expression is calculating as per the Total value of Employee Dimension,

Example, For State_Id 1, it would be : 100 / 3100 = 0.03

For State_Id 2, it is : 400 / 3100 = 0.13

But what is required is that the values must be calculated as per Totals of State_Id

ie, State_Id 1 in "Sum TOTAL Value" should be 100 / 500 = 0.2

State_Id 2 in "Sum TOTAL Value" should be be 400 / 500 = 0.8.

Also, PFA of the Test Application

Any suggestions would be greatly appreciated.

Thanks

Umar

Re: Aggregating the Values Dimension Totals Wise

sunny_talwar — Mon, 02 May 2016 07:52:53 GMT

Try this:

Sum(Value1)/Sum(TOTAL <Employee> Value1)

You basically need to tell the TOTAL qualifier about the field where you want to total on. By just using the TOTAL, it was doing the overall total. When I add <Employee>, it started doing total per Employee

Re: Aggregating the Values Dimension Totals Wise

umartareen — Mon, 02 May 2016 07:59:01 GMT

Works Perfect !

Thank You .

Re: Aggregating the Values Dimension Totals Wise

umartareen — Tue, 10 May 2016 18:04:34 GMT

Hi,

How can this be achieved while using set expressions ?

Lets take an example :

Sum({<Year, Month>} Value1) / Sum(TOTAL {<Year, Month>} Value1)

Thanks,

Umar

Re: Aggregating the Values Dimension Totals Wise

sunny_talwar — Tue, 10 May 2016 18:05:54 GMT

This:

Sum({<Year, Month>}Value1)/Sum(TOTAL <Employee> {<Year, Month>} Value1)

Re: Aggregating the Values Dimension Totals Wise

umartareen — Tue, 10 May 2016 18:14:35 GMT

Solved it !

Sum(<Employee> {<Year, Month>} Value1) / Sum(TOTAL <Employee> {<Year, Month>} Value1)

Regards,

Umar

Re: Aggregating the Values Dimension Totals Wise

sunny_talwar — Tue, 10 May 2016 18:16:31 GMT

umar khan wrote:

Solved it !

Sum(<Employee> {<Year, Month>} Value1) / Sum(TOTAL <Employee> {<Year, Month>} Value1)

Regards,

Umar

This isn't right, are you getting the right results? Should be this

Sum({<Year, Month>}Value1)/Sum(TOTAL <Employee> {<Year, Month>} Value1)

Without <Employee> in the numerator

Re: Aggregating the Values Dimension Totals Wise

davyqliks — Wed, 22 Jul 2020 15:26:36 GMT

This is awesome, thanks... didnt realise i could use a pivot and sub totals like this.

Nice work, thanks

Daniel