How to exclude product group at a chart and scipt level?

[#1]

Hi,

I'm trying to exclude one product group ("Group X") from Product Group list ("Product Groups") so that I don't  have them visible in ListBox so that I don't display them on charts. I can imagine solving that on two levels:

1) Script - which will exclude it from all my analyses

2) Chart / List box level so that I can choose in which analyses I want to have it excluded

Can anybody suggest formulas for both?

- What should be the formula for exclusion in the script?

- How to exclude it at a list box / chart level and where to typ the exclusion (expression field?)

Many thanks

Thomas

[swuehl · Accepted solution]

There is an <Expression> field you can select from the dropdown of the list box properties general tab (at the very bottom of the drop down).

Open the expression editor and type something like

=if([Product Groups] <> 'Group X', [Product Groups])

to filter the Group X out of the available groups in that specific list box.

You can use the same expression as a calculated dimension to filter in a chart (or use a set expression in all your aggregation functions in your chart, like

=sum({<[Product Group] -= {'Group X'}>} Value)

Use a similar expression as above in your load script for a permanent filter:

LOAD

     IF([Product Groups] <> 'Group X', [Product Groups]) AS [Product Groups],

     ...

Both will just eliminate the [Product Group] value 'Group X', but won't remove the associated e.g. sales data for that group, so you may want to filter your records using a WHERE clause instead:

LOAD

     [Product Groups]

FROM ...

WHERE [Product Groups] <> 'Group X';

[#3]

Swuehl,

the formula "=if([Product Groups] <> 'Group X', [Product Groups])" is OK but for some reason nothing happens .

Could there ba a reason for that to happen?

Thanks

[#4]

Got the product name wrong Scripting works. Thanx!

[#5]

One last question:

What formula should I use if I want to exclude the entire product group except for one product (Product Y)?

thanks

[swuehl]

Check for equality instead of difference:

=if([Product Groups] = 'Product Y', [Product Groups])