Filter

[Anonymous]

Please see attached example:

what i want is to add a list with several pre-established conditions, in order to filter the information shown in the table based on the result of the last column:

-100%

<0%

>=0%

-

Is that possible? I've been searching and trying several things, but have not found the right solution...

[prma7799]

Could you please explain bit more? or please share expected output...

thanks

[marcelviegas]

I did not understand

[prma7799]

What is your expected output?

[Anonymous]

Surely the example has few data while I'm working with a lot of years, actual data, budgetted and forecasted (the three origins) data. So the expected output is to quickly select those sales (customeres, products, or both, I do control that part through dimensions) which in the selected sales comparison show increased, decreased, new or lost sales...

It's like the shortcut filter option in access on the (Column(1)-Column(2))/Column(2), predenying the filter options, being the options:

(Column(1)-Column(2))/Column(2)= - (New sales)

(Column(1)-Column(2))/Column(2)=-100% (Lost sales)

(Column(1)-Column(2))/Column(2)>=0 (Increased sales)

(Column(1)-Column(2))/Column(2)<0 (Decreased sales)

[parthesh]

Hello,

I have seen your QVW file and understood your problem,  i think you have to create one calcilated column in script it self with help of if() to show your below desire result in list box.

-100%

<0%

>=0%

-

so that when you select -100% it will show lines which are having -100% in last column.

i can be more clear if you can provide "Base.xlsx".

Thanks.

[Anonymous]

See the attached xlss

That was the first option I've tried, after a lot of work and help it seemed to work but it doesn't. It gives a lot of problems (i.e. with the totals). I've created a calculated dimension that shows directly 'Increased sales', 'Decreased sales', etc. but it fails. So I thought of filtering. It's true that I haven't found any example, but as I'm quiet new on this, I'm not aware if it's possible to filter using variables.

[Anonymous]

Please take into account that the variance column depends on the selected origins and years (and even in the complete expected version, on tdimensions), so it's impossible to add the information in the base...

[prma7799]

Try to create calculated listbox using below expression

((((Sum ({<Y = {"$(=[Año C1])"} , Origen = {"$(=[Origen C1])"}>}[Venta Neta]))

-

(Sum ({<Y = {"$(=[Año C2])"} , Origen = {"$(=[Origen C2])"}>}[Venta Neta]))

)

/(Sum ({<Y = {"$(=[Año C2])"} , Origen = {"$(=[Origen C2])"}>}[Venta Neta])))*100 )

and use if and else condion like below

if([Gw%] >=10 and [Gw%] <=20   ,Green(),

if([Gw%] >20 ,Blue(),

if([Gw%] >0 and [Gw%] <10 ,Black(),

rgb(255,0,0))) )

[madhumitha]

Hello Alex,

Please find attached file.

Does that help?