Skip to main content
Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Filter

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...

16 Replies
prma7799
Master III
Master III

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

thanks

marcelviegas
Creator II
Creator II

I did not understand

prma7799
Master III
Master III

What is your expected output?

Anonymous
Not applicable
Author

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
Creator
Creator

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
Not applicable
Author

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
Not applicable
Author

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
Master III
Master III

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
Creator
Creator

Hello Alex,

Please find attached file.

Does that help?