# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Individual sum of rows

I have a dataset like below.

 Geo Area Sales Conversion Geo1 Area1 100 - Geo1 Area1 - 0.6788 Geo1 Area2 300 - Geo1 Area2 - 0.7893 Geo2 Area3 400 - Geo2 Area3 - 0.3453

I need an expression where it calculates Sales*Conversion, and it should calculate as below.

Geo1->  (100*0.6788)+(300*0.78933) but not as (100+300)(0.6788+0.78933).

1 Solution

Accepted Solutions
Champion III

It's working for me though!!

Expression:

SUM(AGGR(sum(Sales)*sum(Conversion),Geo,Area))

Script

Geo,Area,Sales,Conversion

Geo1,Area1,100,

Geo1,Area1,, 0.6788

Geo1,Area2,300,

Geo1,Area2,,0.7893

Geo2,Area3,400,

Geo2,Area3,,0.3453

];

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
8 Replies
Champion III

Try

sum(AGGR(SUM(Sales*Conversion),Geo,Area))

or

SUM(AGGR(sum(Sales)*sum(Conversion),Geo,Area))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Partner - Contributor III

Hi ashok555,

Please have a look at the below query. Hope this solves your problem.

Geo,

Sum(Converted_Sales) as Converted_Sales

Group By Geo;

Geo,

Area,

Only(Sales) as Sales,

Only(Conversion) as Conversion,

Only(Conversion)*Only(Sales) as Converted_Sales

Group By Geo, Area;

Geo,

Area,

If(Len(Trim(Sales)),Sales) as Sales, //Just replacing blanks with Null

If(Len(Trim(Conversion)),Conversion) as Conversion //Just replacing blanks with Null

INLINE [

Geo, Area, Sales, Conversion

Geo1, Area1, 100,

Geo1, Area1,,0.6788

Geo1, Area2, 300,

Geo1, Area2,,0.7893

Geo2, Area3, 400,

Geo2, Area3,,0.3453

];

Thanks

Dinesh

Champion III

The below is how i would load the data and Expression would be

=SUM(Sales*Expression)

//////////////////////Script/////////////////////////

Sales:

Geo,

Area,

Sales

Geo&'_'&Area as KEY

From yourSource

Where len(trim(Sales))>0;

left join(Sales)

Conversion:

Conversion

Geo&'_'&Area as KEY

From yourSource

Where len(trim(Conversion))>0;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Thank you for your suggestions, but I am not supposed to do anything in the script.

I have to do in the expression only.

Champion III

Then did you try the expressions i posted?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Yes, it is not working giving the result as zero for all areas and geos.

Champion III

It's working for me though!!

Expression:

SUM(AGGR(sum(Sales)*sum(Conversion),Geo,Area))

Script

Geo,Area,Sales,Conversion

Geo1,Area1,100,

Geo1,Area1,, 0.6788

Geo1,Area2,300,

Geo1,Area2,,0.7893

Geo2,Area3,400,

Geo2,Area3,,0.3453

];

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Missed one filter, it's working for me now. Thank you soo much for the prompt help Vineeth...

Here is the link for effective use of AGGR().

https://www.analyticsvidhya.com/blog/2014/02/aggr/

Community Browser