Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have doubts in one Set Analysis expression.
I intend to restrict the month for expression1, depending on the months with results for expression 2.
I think the problem is in the quoting.
Someone can help me.
thank you
Expression1:
sum({<MonthID = {">=$(=Max(MonthID) -11)<=$(=Max(MonthID))"},
CalendarType = {$(=vCalendarType)},
MonthOrder = ,
Date = {"<=$(=Max(Date))"},
Year = ,
FiscalYear = ,
Quarter = ,
Week = ,
[Period (#)] = ,
Month =
,[Brand Code]={'=(365-Max( $(vSetRolling12) Date) + Min( $(vSetRolling12) Date)) <$(vL4LNrDays) and Sum({$(vRolling12)} _Inactive_Brand)<1'}>}
[#Turnover EUR])
Expression2
sum({<MonthID = {">=$(=Max(MonthID) -23)<=$(=Max(MonthID)-12)"},
CalendarType = {$(=vCalendarType)},
MonthOrder = ,
Date = {"<=$(=Max(Date))"},
Year = ,
FiscalYear = ,
Quarter = ,
Week = ,
[Period (#)] = ,
Month= ,
[Brand Code]={'=(365-Max( $(vSetRolling12) Date) + Min( $(vSetRolling12) Date)) <$(vL4LNrDays) and Sum({$(vRolling12)} _Inactive_Brand)<1'}>}
[#Turnover EUR])
Hi.
Your set expression is rather complex.
First, I suggest you to test each part separately.
For example:
sum({<MonthID = {">=$(=Max(MonthID) -23)<=$(=Max(MonthID)-12)"}>} [#Turnover EUR])
If this one doesn't work properly, try an explicit form:
sum({<MonthID = {"=MonthID>=$(=Max(MonthID) -23) and MonthID<=$(=Max(MonthID)-12)"}>} [#Turnover EUR])
Also, your expression is a bit tricky as the variables are used with $-sign expansion.
Depending on what is the values of $(vSetRolling12) or $(vRolling12) the resulting expression may be wrong.
As a guess, In the string "and Sum({$(vRolling12)} _Inactive_Brand)<1'}" there is no <> signs around the expansion $(vRolling12). If they not present in vRolling12, it results in a wrong syntax.
I think you have an extra closing curvy bracket after Sum({$(vRolling12)}
Hi Oleg and whiteline,
Both expressions are ok and work perfectly.
I think I did not make myself understood.
I intend to condition expression1, by returned months in expression2.
Many Thanks
Bruno
Bruno,
Set Analysis expressions cannot be conditioned by other expressions, or by any dimensions, because Set Analysis condition is calculated only once per chart, globally, with no regard to dimensions and other expressions.
Oleg,
many thanks by your contribution.
Do you have any suggestion, to achieve my need?
Thanks
Bruno
Hi, Bruno.
Could you explain your needs more?
As you described only the way you want to use to achive it.
Hi whiteline,
I've a SetAnalysis(Rolling12) (Expression1) that it's ok. I've a similar one, for the previous rolling12 (Expression2).
I need to build a Like for Like expression, similar to expression1, but where month exists in expression2.
Sum({<MonthID = {">=$(=Max(MonthID)-11)<=$(=Max(MonthID))"},
CalendarType = {$(=vCalendarType)},
MonthOrder = ,
Date = {"<=$(=Max(Date))"},
Year = ,
FiscalYear = ,
Quarter = ,
Week = ,
[Period (#)]= ,
Month= {'Jul','Aug'}, //I need this dynamic, dependent from possible months in expression2//
[Brand Code]={'=(365-Max($(vSetRolling12) Date) + Min( $(vSetRolling12) Date)) <$(vL4LNrDays) and Sum({$(vRolling12)} _Inactive_Brand)<1'}
>} [#Turnover EUR])
It's possible to concatenate the possible months, returned by expression2 in a variable?
Thanks
Bruno
Sorry, Bruno.
I still don't understand, what means 'possible months, returned by expression2'. Your expression2 is just the same sum as expr1 just 12 months back.
And what means dynamic month, should it be selected by user or be dependent on dimension value (The last one as Oleg mentioned above isn't possible with set analysis).
Hi whiteline,
Consider this example, please.
I've two columns, one with Current MAT (Rolling12) and other with Previous MAT.
I pretend to show in Current MAT column, the value for the months that exists in previous MAT (Jul and Aug).
In the real case, I don't have the month as Dimension at chart.
Any doubt, ask please.
Thanks
Bruno