Webi 4.1 - Filter on Count Value.

[Former Member]

Hi Folks, Super Novice here.

I have a report in webi which I would like to add a filter. I Only want to show the product that has a forecast count greater than 4.

When I filter on count = greater than 4, all the data on the table goes away.

I tried the following as my count variable definition: And I got the same result

***Forecast in the table = Totalsalesforecast*****

=Count([Totalsalesforecast];All) In([Shop];[Product])

=Count([Totalsalesforecast]) In([Shop];[Product])

=Count([Totalsalesforecast])

Can Someone please help?.

SHOP	Product	Description	key Figure	2/1/2016	2/2/2016	2/3/2016	2/4/2016	2/5/2016	2/6/2016	2/7/2016	2/8/2016	2/9/2016	Count
SHOP A	111111	item a	Forecast	2	8	9	8	9	4	2	1		8
SHOP A	111111	item a	Actual
SHOP A	222222	Item B	Forecast								1	2	2
SHOP A	222222	item B	Actual
SHOP A	333333	Item C	Forecast									2	1
SHOP A	333333	Item C	Actual
SHOP A	444444	Iitem D	Forecast								1	4	2
SHOP A	444444	Item D	Actual
SHOP A	555555	item e	Forecast	1									1
SHOP A	555555	item e	Actual
SHOP A	666666	item f	Forecast	1									1
SHOP A	666666	item f	Actual
SHOP A	777777	item g	Forecast	1									1
SHOP A	777777	item g	Actual
SHOP A	222222	item h	Forecast	1									1
SHOP A	222222	item h	Actual
SHOP A	333333	item i	Forecast	1	4	4	4	4	2	1	1	1	9
SHOP A	333333	item i	Actual

[amitrathi239 · Accepted solution]

Hi,

try this.

Var=If(sum(Count([Totalsalesforecast] In([Shop];[Product];[Date]))>4)) then "Show" else "Hide"

Filter on Var where equal to Show.

Amit

[Former Member · Accepted solution]

Thanks alot Amit for the quick reply, im getting an error with the command you provided:

"The Expression or Sub-expression at position 4 in the 'if' function uses an invalid data type. (IES 10037)"

im not too sure what that means. Thanks again.

Ademola

[amitrathi239 · Accepted solution]

Hi,

Check this.

Var=If(sum(Count([Totalsalesforecast]) In([Shop];[Product];[Date]))>4) then "Show" else "Hide"

Amit

[Former Member · Accepted solution]

Thanks again Amit. the end result is still the same everything disappears when I set this variable  to "Show", however when i set it to "Hide" the whole table shows up.

I tried using this variable in the value cell and it all populated "Hide".

Thanks

[Former Member · Accepted solution]

seems as though the Sum function is not working and the filter is being applied to the binary values.

[amitrathi239 · Accepted solution]

Hi,

Add below formula in the total column.

Var Total=Count([Key Figure]) In ([Product];[ Description];[Shop])

After that apply filter on Var Total where grater then equal to 4.

Amit

[Former Member · Accepted solution]

Key Figure and Description are not objects of the report, they are just text i Keyed in as a header for the column

If I substitute these with the objects they represent, then Ill end up with this:

=Count([Totalsalesforecast]) In([Product];[Product descr];[Shop])  When I filter Total to = greater  than 4. nothing changes.

[Former Member · Accepted solution]

Oh my God! I had it all along. I had some filters set at the wrong level. thanks your command worked. Thanks.