Set expression behaves unexpected when multiplying with a dimension value

[wdonckers]

Hi,

I would like to show all Months' values up to and including the Month that is selected.

for instance when I select 2014 and Feb  i should get

Month     Amount

Jan          100

Feb          220

I tried using the following expression:

Sign *

sum({$<

  [Value Type for Reporting_VTYPE]={'010'},

  Version_VERSION=,

  Month={"<=$(=max({<Year={$(=max(Year))}>} Month))"},

  Quarter=,

  Year = {$(=max(Year))}

  >}[Sales for the Period_SALES])

the result is :

Month     Amount

Feb          220          So only the month selected is shown, and the <= selection seems to have no effect.

The "Sign" is a field in the datamodel than contains a 1 or minus 1 in order to change the sign depending on the reporting line.

In my example I would expect it to work because Sign is unambiguously known (I see that when including it as a dimension)

If I leave the "Sign *" part out then the result is :

Month     Amount

Jan          -100

Feb          -220

--> the months are ok but the signs are not.

So, can a calculated set expression not be used in another calculation?

What is the reason behind this?

thanks for sharing your view with me.

rgds,

Wim

[lironbaram · Accepted solution]

sign is your problem

because when you select February

all records in jan don't have records in sign field so you need to put sign inside the set expression

sum({$<

  [Value Type for Reporting_VTYPE]={'010'},

  Version_VERSION=,

  Month={"<=$(=max({<Year={$(=max(Year))}>} Month))"},

  Quarter=,

  Year = {$(=max(Year))}

  >}[Sales for the Period_SALES]*sign)

[MK_QSL]

Try something like below...

Month = {"<=$(=Max(Month))"}, Year = {'$(=Max(Year))'}