Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
Try something like below...
Month = {"<=$(=Max(Month))"}, Year = {'$(=Max(Year))'}