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

[raajeshn]

Hi Jonthan,

Thanks for the response and sorry - could not manage time earlier to create this mock.

First - my problem is, I dont want the rows to be hidden - rather they should be seen and the chart should not listen to the filters.

Please find attached the mock, where I am showing data for the past 6 months (based on the max(month)). The chart shows correct numbers when nothing is selected. However, when we select either Product or Product Category - the expression listens only to this selection. For Ex, If am selecting P1 in the selected example for Company Products, it shows null value in P2's denominator.

FYI - Actually I am showing only Company Products in the filter (th other two - Competitor Products and Other Products are are just for reference)

Hi Kiran, sorry could not create the mock earlier. Please find it attached now

Appreciate your support.

Thanks & Regards,

Raajesh N

[raajeshn]

Hi Jonathan / Kiran..

Sorry to bother you guys - buty any luck on this issue...? Thanks.

Regards,

Raajesh N

[swuehl · Accepted solution]

Maybe this for a start:

Use

=only({1}AGGR(NODISTINCT SUM({<MONTH_YEAR = {">=$(vTimeStart)<=$(vTimeEnd)"}, PRODUCT_CATEGORY=, PRODUCT=> }SALEAMOUNT), PRODUCT_CATEGORY))

as denominator expression.

A selection in PRODUCT or PRODUCT_CAT will not change your numbers.

See attached.

edit:

If you want to select also in your Competitor or Company Products, I think you also need to clear the COMPANY_FLAG in above set expression within the sum.

[raajeshn]

Hi Swuehi,

Thanks a ton - your expression is working like a charm except that it was listening to Product Selections. However, You had the answer for this as well, as company was also getting selected when we select the Product from the Filter. Hence, I included the COMPANY= in the set expression and it is working. Once again, thanks a ton.

one more request - If time permits, can you help me understand this ONLY Function here.I went through the help but I guess I need little more details on how its working with this AGGR Statement. Thanks.

Regards,

Raajesh N

[swuehl]

The Help says about the aggr() function:

...

By default, the aggregation function will aggregate over the set of possible records defined by the selection.

...

That's the reason why you first saw only records for your selected or possible products.

I think the HELP section about aggr() is incorrect in describing (at least in my version) that you can use a set expression within the aggr() to define another set:

aggr ([ distinct | nodistinct ] [{set_expression}]expression {, dimension})

....

An alternative set of records can be defined by a Set Analysis expression.

....

I don't think this works as described. So I use an outer aggregation function to define the new set, which will determine the aggr() dimension values used (i.e. all). I chose only() aggregation function because I believe it's most appropriate here.

[raajeshn]

Thanks for the swift response. I understand the reason now. Thanks a ton.

Regards,

Raajesh N