topic Re: How to exclude product group at a chart and scipt level? in QlikView

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

Sat, 25 Jan 2014 11:03:44 GMT

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

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

swuehl — Sat, 25 Jan 2014 11:34:45 GMT

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';

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

Sat, 25 Jan 2014 12:17:43 GMT

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

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

Sat, 25 Jan 2014 12:23:34 GMT

Got the product name wrong Scripting works. Thanx!

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

Sat, 25 Jan 2014 12:44:47 GMT

One last question:

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

thanks

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

swuehl — Sat, 25 Jan 2014 12:58:11 GMT

Check for equality instead of difference:

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