Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

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


MVP & Luminary
MVP & Luminary

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

Partner
Partner

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

MVP & Luminary
MVP & Luminary

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.

Partner
Partner


Oleg,

many thanks by your contribution.

Do you have any suggestion, to achieve my need?

Thanks

Bruno

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.

Partner
Partner

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

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

Partner
Partner

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