Need help in set analysis for group sum

[musketeers]

Hi All,

I need to create a Bar chart based on the following conditions:

Dimention: Year+ Quarter  (Example: 2012Q1, 2012Q2). I have a field which contain this data.

Expression: I need to show the sum of yearly units for given products, of selected country. Like if user has selected US as a country, I need to display SUM of yearly units of all products listed under country US. I have done this. But in some cases, we have two types of sub products for some products. In that case we have to choose only one sub product and then have to display sum of yearly units. Example

country	base_prod	Product	FY_QTR	UNITS_1	UNITS_YR
AUSTRIA	ALV4715	Nestle_Ind	2011Q2	10149	40596
AUSTRIA	ALV3415	Nestle_Ind	2011Q2	610	2440
AUSTRIA	ALV4715	Nestle_Ind	2011Q2	10249	40346
AUSTRIA	ALV3415	Nestle_Ind	2011Q2	7810	2140
AUSTRIA	ALV4715	Nestle_Ind	2011Q2	22149	2596
AUSTRIA	ALV3415	Nestle_Ind	2011Q2	810	2540

Here in above table, If user has selected AUSTRIA as a country, then for FY_Qtr = 2011Q2, for Product Nestle_Ind, I have to display sum of UNITS_YR. But here  we have two base product for Product Nestle_Ind. I need to consider only one, either ALV4715 or ALV3415. Now to choose one I have to group by base product and take the sum of field UNITS_1 and then have to take the highest sum. that highest sum will decide the base product we have to take. If Units_1 sum for ALV4715 is highest we have to take ALV4715 other wise ALV3415.

I want to know how can i write set analysis to select onle base_prod based on the above business logic.

Please help me in this issue.

Thanks

[Anonymous · Accepted solution]

Hi Musketeers,

Using the below expression only the base product with highest value will be displayed

=max(aggr(Sum(UNITS_YR),country,FY_QTR,Product,base_prod))

Using the below expression the name of the base product will be displayed in the cahrt

=FirstSortedValue(base_prod, -aggr(Sum(UNITS_1),country,FY_QTR,base_prod))

I think adding Product also as Dimension will give a good chart.

Shivaji..

[musketeers]

Hi Shivaji,

thanks for ur answer. it worked for me. thanks for ur support.......