Expression is not getting the right nrs

[alec1982]

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,

[NZFei]

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

)

[alec1982]

Hi,

Thank you for your reply,

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

Thxs,

[NZFei]

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

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

[Year of Expense] = varPlReportingYear

[alec1982]

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

[NZFei · Accepted solution]

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

)

[alec1982]

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,