Announcements
cancel
Showing results for
Did you mean:
Creator

SET ANALYSIS COMPLEX

I have to do this calculation, it tells me that the formula is syntactically ok, but really the analysis set does not work? what am I wrong?

=if(GetSelectedCount (MONTH)=12 or GetSelectedCount (MONTH)=0,

sum({<YEAR = {\$(=max(YEAR))},CODE={"1*"}>}SALES),
if(sum({<YEAR = {\$(=max(YEAR)-1)},CODE={"1*"}>}SALES)>0,

[sum({<YEAR = {\$(=max(YEAR)-1)},CODE={"1*"}>}SALES)

*sum({<YEAR = {\$(=max(YEAR))},CODE={"1*"}>}SALES)

/sum({<YEAR = {\$(=max(YEAR)-1)},CODE={"1*"}>}SALES)],

sum({<YEAR = {\$(=max(YEAR))},CODE={"1*"}>}SALES)]/Count(DISTINCT {<YEAR = {\$(=max(YEAR))} >} MONTH)*12))

Labels (2)

• Set Analysis

1 Solution

Accepted Solutions
MVP

It's not going to work with the square brackets you have in the expression (and I'm not sure why they are there to begin with):

=if(GetSelectedCount (MONTH)=12 or GetSelectedCount (MONTH)=0,
sum({<YEAR = {\$(=max(YEAR))},CODE={"1*"}>}SALES),
if(sum({<YEAR = {\$(=max(YEAR)-1)},CODE={"1*"}>}SALES)>0,
[sum({<YEAR = {\$(=max(YEAR)-1)},CODE={"1*"}>}SALES)
*sum({<YEAR = {\$(=max(YEAR))},CODE={"1*"}>}SALES)
/sum({<YEAR = {\$(=max(YEAR)-1)},CODE={"1*"}>}SALES)],
sum({<YEAR = {\$(=max(YEAR))},CODE={"1*"}>}SALES)]/Count(DISTINCT {<YEAR = {\$(=max(YEAR))} >} MONTH)*12))

2 Replies
MVP

It's not going to work with the square brackets you have in the expression (and I'm not sure why they are there to begin with):

=if(GetSelectedCount (MONTH)=12 or GetSelectedCount (MONTH)=0,
sum({<YEAR = {\$(=max(YEAR))},CODE={"1*"}>}SALES),
if(sum({<YEAR = {\$(=max(YEAR)-1)},CODE={"1*"}>}SALES)>0,
[sum({<YEAR = {\$(=max(YEAR)-1)},CODE={"1*"}>}SALES)
*sum({<YEAR = {\$(=max(YEAR))},CODE={"1*"}>}SALES)
/sum({<YEAR = {\$(=max(YEAR)-1)},CODE={"1*"}>}SALES)],
sum({<YEAR = {\$(=max(YEAR))},CODE={"1*"}>}SALES)]/Count(DISTINCT {<YEAR = {\$(=max(YEAR))} >} MONTH)*12))

Creator
Author

oops, I must have really crossed my eyes, thank you very much...