AGGR and SET ANALYSIS

[raajeshn]

Hi Experts,

I have scenario where I would like to compare the sale of a particular product with all Products of the category - I use the following expression

=AGGR (NODISTINCT SUM({ <QUARTER_YEAR = {">=$(vFiscalStart)<=$(vFiscalEnd)"}, [PRODUCT_CATEGORY]=, PRODUCT=> }  SALES), [PRODUCT_CATEGORY])

This fetches me the expected results when no Product / Product Category is selected. However, When I select a Product / Product Category, I am seeing the correct numbers only for the selected Product / Product Category. How do I ensure, that my results are not changed, when there is a selection happening in either of Product or Product Category.

Any help is highly appreciated.

Thanks & Regards,

Raajesh N

[#2]

Raajesh,

Try the following:

Create a straight table with product category and product as dimensions.

Expression as:

Sum({ <QUARTER_YEAR = {">=$(vFiscalStart)<=$(vFiscalEnd)"}>} Sales)/
Sum(Total <PRODUCT_CATEGORY> { <QUARTER_YEAR = {">=$(vFiscalStart)<=$(vFiscalEnd)"}>} Sales)

Regards,

Kiran Rokkam

[raajeshn]

Hi Kiran,

Thanks for the quick response. But, I guess - I have the same problem - I dont want the expression to listen to the selectionsb- it does here. Moreover, the numbers are not matching. Appreciate your help.

Thanks & Regards,

Raajesh N

[#4]

Raajesh:

Need more details to debug. Can you scramble the data and share you qvw or some screenshots?

Regards,

Kiran Rokkam.

[raajeshn]

Sure Kiran. But I would need some time for this - I will attach it before tomorrow. Thanks a lot for your support.

Regards,

Raajesh N

[jonathandienst]

Raajesh

I assume the aggr expression is a calculated dimension. Does the chart expression also override the product/product category selections?

Jonathan

[#7]

Try this

=AGGR (NODISTINCT SUM({ 1<QUARTER_YEAR = {">=$(vFiscalStart)<=$(vFiscalEnd)"}, [PRODUCT_CATEGORY]=, PRODUCT=> }  SALES), [PRODUCT_CATEGORY])

[raajeshn]

Hi Yogananthan,

I have already tried this, and it also produces the same result. Appreciate your support.

Thanks & Regards,

Raajesh N

[raajeshn]

Hi Jonathan,

Thanks for the response. I dont know what made you assume that the expression is a calculated dimension - but you are right, I am actually comparing the company's products with competitor's products and hence there is a calculated expression, which will be like this

=IF(COMPANY='MY_COMPANY', PRODUCT).

However, I am not understanding your second question. What is the chart expression you are referring here. I have two expressions in my chart, one showing the actual sales of the Product - second showing the sales of the competitor products. This is my first expression.

=SUM({1<QUARTER_YEAR = {">=$(vFiscalStart)<=$(vFiscalEnd)"}, PRODUCT=, [PRODUCT_CATEGORY]=>}SALES)

And my second expression is as follows:

=AGGR (NODISTINCT SUM({ <QUARTER_YEAR = {">=$(vFiscalStart)<=$(vFiscalEnd)"}, [PRODUCT_CATEGORY]=, PRODUCT=> }  SALES), [PRODUCT_CATEGORY])

Both these expressions are working good when no product / product category is selected. However, once either of them are selected, the graph reacts accordingly - i.e, if a product is selected, the table has numbers only for the selected product and nulls in all other rows. If I am select a product category, then all the products of the category has numbers while the other rows return null values. The above issue is available only in the Second expression, while the first expression behaves as expected.

I am not sure I understood your question and that is the reason for this long explanation (not sure, if I still answered you). Sorry for that. Appreciate your support

Thanks & Regards,

Raajesh N

[jonathandienst]

Raajesh

OK, you are overriding the selections in the expressions. So now you need to suppress the rows with nulls/zeros.

First check that the Suppress Zeros check box is ticked. If it still displays the rows, you have at least one more expression that is returning something other than zero or null (a blanks string will cause the row to display).

You can force this expression to return zero if the above expressions are zero by modifying the expression:

     = If(Column(1) = 0, 0, third expression)

Also suppress null values for the calculated dimension.

If these suggestions don't solve the problem, I suggest you post your app or a sample illustrating the problem.

Hope that helps

Jonathan