Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bruno_m_santos
Partner - Creator
Partner - Creator

Set Analysis restrict months depending on other expression

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])


14 Replies
whiteline
Master II
Master II

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.


Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

I think you have an extra closing curvy bracket after Sum({$(vRolling12)}

bruno_m_santos
Partner - Creator
Partner - Creator
Author

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

bruno_m_santos
Partner - Creator
Partner - Creator
Author


Oleg,

many thanks by your contribution.

Do you have any suggestion, to achieve my need?

Thanks

Bruno

whiteline
Master II
Master II

Hi, Bruno.

Could you explain your needs more?

As you described only the way you want to use to achive it.

bruno_m_santos
Partner - Creator
Partner - Creator
Author

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

whiteline
Master II
Master II

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).

bruno_m_santos
Partner - Creator
Partner - Creator
Author

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

mat.jpg