Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Individual sum of rows

I have a dataset like below.

  

  

GeoAreaSales Conversion
Geo1Area1100    -
Geo1Area1-   0.6788
Geo1Area2300-
Geo1Area2-0.7893
Geo2Area3400-
Geo2Area3-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
vinieme12
Champion III
Champion III

It's working for me though!!

Expression:

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

Script

LOAD * 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

];

2sumaggr.JPGsumaggr.JPG

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

View solution in original post

8 Replies
vinieme12
Champion III
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.
dineshsingh
Partner - Contributor III
Partner - Contributor III

Hi ashok555,

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

LOAD

  Geo,

  Sum(Converted_Sales) as Converted_Sales

Group By Geo;

LOAD

  Geo,

  Area,

  Only(Sales) as Sales,

  Only(Conversion) as Conversion,

  Only(Conversion)*Only(Sales) as Converted_Sales

Group By Geo, Area;

Load

  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

vinieme12
Champion III
Champion III

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

=SUM(Sales*Expression)

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

Sales:

LOAD

Geo,

Area,

Sales

Geo&'_'&Area as KEY

From yourSource

Where len(trim(Sales))>0;

left join(Sales)

Conversion:

LOAD

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.

vinieme12
Champion III
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.

vinieme12
Champion III
Champion III

It's working for me though!!

Expression:

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

Script

LOAD * 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

];

2sumaggr.JPGsumaggr.JPG

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/