(Sub)Totals for column of only the values >0

[gembreda]

Hi all,

I would like to get some help on the following issue:

In one of our reports I used a formula to get the difference between column A and B (If( - > 0.019, - , null() ) ). In our report I want the total sum of each column. Qlikview gives us result 1 but we want result...

Could someone please help me solve our problem? We want totals for every Department and every Company as well.

Company	Department	Position	A	B	A-B (if >0)	B-A (if >0)
QT	East	Manager	1	1
		Advisor	4	2	2
		Consultant	3	2	1
		Assistant	2	4		2
Result 1	Total of East		10	9	1
Result 2	Total of East I would like to see		10	9	3	2

[#2]

Hi,

Try using Pivot Table to get column wise Sub Totals.

Best of luck

[sujeetsingh]

Gam just go to

Properties<<<<<Expression<<<<

on Expression tab there is Total Mode and check the last option and select by drop down what you want sum,count,avg etc of rows.

Hope this help

[perumal_41]

Hi,

use dimensionality ( )  function.,may be solve  ur problems

Regards

Perumal A

[gembreda]

Thanks for your answer. I did put the data in a pivot table but this did not change the way the total is calculated.

Do you have another option?

I tried to use the option via expressions etc, but the 'total modus' is greyed out.Can not find a setting that changes this.

[gembreda]

I found out that your solution works if I select a straight table. In our case we need to use a pivot table.

[jonathandienst]

Hi

Try these - I am not sure if you need the inner sum or not):

Sum(Aggr(If( - > 0.019, - ), Company, Department, Position))

or

Sum(Aggr(Sum(If( - > 0.019, - )), Company, Department, Position))

This assumes that Company, Department and Position are the pivot table dimensions.

Regards

Jonathan

[er_mohit]

department	position	sum(A)	sum(B)	SUM(If(->0.019,-,NULL()))	SUM(If(->0.019,-,NULL()))
EAST	advisor	4	2	2	0
	assistant	2	4	0	2
	consultant	3	2	1	0
	manager	1	1	0	0
	Total	10	9	3	2

use position & department as dimension and then  in presentation choose partial sum on position and use expression as given above of column header...

hope this help you.....

[#9]

USE IT, HOPE IT HELP YOU.

firstly use PIVOT TABLE.

step1--here department and position are dimension.

step 2-- select presentation then select position then select partial sum

step 3---sum(A) , sum(B) and other two columns are expressions.

department	position	sum(A)	sum(B)	SUM(If(->0.019,-,NULL()))	SUM(If(->0.019,-,NULL()))
EAST	advisor	4	2	2	0
	assistant	2	4	0	2
	consultant	3	2	1	0
	manager	1	1	0	0
	Total	10	9	3	2

[sujeetsingh]

budyy.. I changed according to you in your sample and it worked..Totals are visible there

And I am not able to send any images right now..else i might have shon you