Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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..
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..
Hi Shivaji,
thanks for ur answer. it worked for me. thanks for ur support.......