topic Re: Filtering inside an expression is not working in QlikView

Filtering inside an expression is not working

alec1982 — Tue, 11 Sep 2012 14:09:41 GMT

hi guys,

I have the following expression which is not filtering correctly when applied in a chart.

sum(

if ((([Year of Expense] = $(varPlReportingYear))

and (Index([Expense Type (annual)], 'Rent') > 0)

and (Index([Lease Type], 'Lease') > 0))

, [Amount of Expense (USD-annual)]

)

I tried to have text boxof sum of the expense amount and applied the filters through list boxes and worked fine but when I added it inside an expression, it doesn't.

Anybody can help.

Thxs,

Alec

Re: Filtering inside an expression is not working

jonathandienst — Tue, 11 Sep 2012 14:29:15 GMT

This is your expression converted from sum(if())

Sum({<[Year of Expense] = {$(varPlReportingYear)},

[Expense Type (annual)] = {"*Rent*"},

[Lease Type] = {"*Lease*"}>} [Amount of Expense (USD-annual)])

But your expression looks OK, so this might also not work as it should produce the same results, but give it a try...

Regards

Jonathan

Re: Filtering inside an expression is not working

alec1982 — Tue, 11 Sep 2012 15:56:59 GMT

This is great.

Thxs,

Re: Filtering inside an expression is not working

alec1982 — Thu, 13 Sep 2012 13:43:40 GMT

hi Jonathan,

I run int the same issue on another chart and wasn't able to figure it out:

here is the expression, Please let me know if you can help.

if (

Sum( if ( ((Index([Expense Type (annual)], 'Rent') = 0) or GetFieldSelections(_ShowRentFlag)=1)

and ([Year of Expense] = $(varPlReportingYear))

,[Amount of Expense (USD-annual)]

))

> 0

Sum(if ( ((Index([Expense Type (annual)], 'Rent') = 0) or GetFieldSelections(_ShowRentFlag)=1)

and ([Year of Expense] = $(varPlReportingYear))

, [Amount of Expense (USD-annual)]

)

) / 1000

)

Thxs,

Re: Filtering inside an expression is not working

jonathandienst — Thu, 13 Sep 2012 14:40:04 GMT

What are you trying to achieve with GetFieldSelections() here?

Re: Filtering inside an expression is not working

alec1982 — Thu, 13 Sep 2012 15:11:00 GMT

On this bar chart the dimenssion is Expense Type. the get field selection is related to a toggle to include/ exclude one of the expense types.

the value of the field selection changes based on clicking on a little text box on top of the chart.

Thxs for your help.

Re: Filtering inside an expression is not working

alec1982 — Mon, 17 Sep 2012 18:56:01 GMT

Hi Jonathan,

I have attached a copy of the qvw including the chart and how the toggle works.

The numbers on this qvw are correct but when I use the same chart and the same fields on the requiered qvw it is giving me higher nrs.

I appreciate any help.

Thxs,