topic Re: Filter in QlikView

Filter

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Filter

prma7799 — Tue, 24 Oct 2017 10:33:28 GMT

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

thanks

Re: Filter

marcelviegas — Tue, 24 Oct 2017 10:34:24 GMT

I did not understand

Re: Filter

prma7799 — Tue, 24 Oct 2017 10:38:14 GMT

What is your expected output?

Re: Filter

Anonymous — Tue, 24 Oct 2017 10:49:40 GMT

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)

Re: Filter

parthesh — Tue, 24 Oct 2017 10:54:17 GMT

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.

Re: Filter

Anonymous — Tue, 24 Oct 2017 10:59:14 GMT

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.

Re: Filter

Anonymous — Tue, 24 Oct 2017 11:03:10 GMT

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

Re: Filter

prma7799 — Tue, 24 Oct 2017 11:03:22 GMT

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

Re: Filter

madhumitha — Tue, 24 Oct 2017 11:07:03 GMT

Hello Alex,

Please find attached file.

Does that help?

Re: Filter

Anonymous — Tue, 24 Oct 2017 11:13:04 GMT

The calculated listbox only returns one value (teh total variance). Maybe I didn't undestood correctly your solution...

Re: Filter

Anonymous — Tue, 24 Oct 2017 11:21:31 GMT

That's what I mentiones, it seems to work, but if you click the first decreased sales:

You get this:

And there are other decreased sales... Even if you change the expression using original references (not column(1), Column(2), etc.) it doesn't work properly...

Re: Filter

Anonymous — Tue, 24 Oct 2017 11:36:08 GMT

I've included

LOAD * INLINE [

'Variance'

'New sales'

'Lost sales'

'Increased sales'

'Decreased sales'

];

in order to create a list I understand I can't use to manage a variable to apply a filter, but I still can't find the way to apply filters...

Re: Filter

Anonymous — Tue, 24 Oct 2017 11:57:49 GMT

Is there any action linked to a button I could explore/use??

Re: Filter

Anonymous — Tue, 24 Oct 2017 12:15:39 GMT

Select field seems to be an option, but what if it's an expression, not a field?

Re: Filter

madhumitha — Wed, 25 Oct 2017 05:06:19 GMT

Seems like its very tough to do Alex. I will keep trying your requirement and post if I could get through.

Re: Filter

Anonymous — Wed, 25 Oct 2017 07:10:04 GMT

I made it reviewing the calculated dimension. It seems that I made a mistake. Thanks for your help.