Individual sum of rows

[Anonymous]

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).

[vinieme12]

Try

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

or

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

[dineshsingh]

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]

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;

[Anonymous]

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]

Then did you try the expressions i posted?

[Anonymous]

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

[vinieme12 · Accepted solution]

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

];

[Anonymous]

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/