Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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';
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';
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
Got the product name wrong Scripting works. Thanx!
One last question:
What formula should I use if I want to exclude the entire product group except for one product (Product Y)?
thanks
Check for equality instead of difference:
=if([Product Groups] = 'Product Y', [Product Groups])