topic Re: Expression is not getting the right nrs in QlikView

Expression is not getting the right nrs

alec1982 — Mon, 10 Sep 2012 20:37:36 GMT

hi guys,

Not sure what's wrong with this expression :

sum(

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

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

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

, [Amount of Expense (USD-annual)]

)

it is getting me a wrong nr like I am getting 169 millions instead of 77 millions.

If I put the fields in a table and apply three filters for Year of expense and expense type(annual) and Lease type I get the right nrs.

I tried to do Sum([Amount of Expense (USD-annual)]) and apply the filters separately and it worked fine but when I use it in expression it doesn't.

Any ideas?

Thxs,

Re: Expression is not getting the right nrs

NZFei — Tue, 11 Sep 2012 02:53:04 GMT

Can you try this:

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

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

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

sum([Amount of Expense (USD-annual)])

)

Re: Expression is not getting the right nrs

alec1982 — Tue, 11 Sep 2012 03:31:43 GMT

Hi,

Thank you for your reply,

I tried it but it gave me null value(-)

Thxs,

Re: Expression is not getting the right nrs

NZFei — Tue, 11 Sep 2012 03:43:24 GMT

Are you sure [Year of Expense] = $(varPlReportingYear) works well?

My last suggestion is to try it without $().

[Year of Expense] = varPlReportingYear

Re: Expression is not getting the right nrs

alec1982 — Tue, 11 Sep 2012 03:51:54 GMT

Hi,

Thank you for your reply.

I have added $(varPlReportingYear) into a text box and returned the right value. and Year of Expense is the right field name.

[Year of Expense] = varPlReportingYear doesn't help

Thxs,

Alec

Re: Expression is not getting the right nrs

NZFei — Tue, 11 Sep 2012 04:24:24 GMT

If you put =$(varPlReportingYear) into a text box, it will work. However if you try it in set analysis, it will not work. Variables can not pass value to fields like that.

I attatch a small sample file to explain to you.

This should work for your case:

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

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

sum({$<[Year of Expense] = {"=$(varPlReportingYear)"}>}[Amount of Expense (USD-annual)])

)

Re: Expression is not getting the right nrs

alec1982 — Tue, 11 Sep 2012 05:00:15 GMT

Hi,

Thank you so much for your reply,

I guess that in the sample above the same problem is occuring.

I have added a chart that looks exactly the same called Current year cost.

Let me know your thoughts.

Thxs,