Filtering inside an expression is not working

[alec1982]

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

[jonathandienst · Accepted solution]

Hi

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

[alec1982]

This is great.

Thxs,

[alec1982]

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,

[jonathandienst]

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

[alec1982]

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.

[alec1982]

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,