topic Re: Set Analysis restrict months depending on other expression in QlikView

Set Analysis restrict months depending on other expression

bruno_m_santos — Fri, 23 Aug 2013 08:38:09 GMT

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

Re: Set Analysis restrict months depending on other expression

whiteline — Sat, 24 Aug 2013 19:44:12 GMT

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.

Re: Set Analysis restrict months depending on other expression

Oleg_Troyansky — Sun, 25 Aug 2013 11:32:10 GMT

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

Re: Set Analysis restrict months depending on other expression

bruno_m_santos — Mon, 26 Aug 2013 08:42:43 GMT

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

Re: Set Analysis restrict months depending on other expression

Oleg_Troyansky — Mon, 26 Aug 2013 13:12:57 GMT

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.

Re: Set Analysis restrict months depending on other expression

bruno_m_santos — Tue, 27 Aug 2013 10:46:48 GMT

Oleg,

many thanks by your contribution.

Do you have any suggestion, to achieve my need?

Thanks

Bruno

Re: Set Analysis restrict months depending on other expression

whiteline — Tue, 27 Aug 2013 10:57:11 GMT

Hi, Bruno.

Could you explain your needs more?

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

Re: Re: Set Analysis restrict months depending on other expression

bruno_m_santos — Tue, 27 Aug 2013 11:15:54 GMT

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

Re: Re: Set Analysis restrict months depending on other expression

whiteline — Tue, 27 Aug 2013 14:03:47 GMT

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

Re: Re: Re: Set Analysis restrict months depending on other expression

bruno_m_santos — Tue, 27 Aug 2013 22:02:18 GMT

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

Re: Set Analysis restrict months depending on other expression

whiteline — Thu, 29 Aug 2013 08:00:48 GMT

I think you could use P() function to get the months from the expression2 with the same set expression (bolded).

Sum({<MonthID = {">=$(=Max(MonthID) -11)<=$(=Max(MonthID))"},

CalendarType = {$(=vCalendarType)},

MonthOrder = ,

Date = {"<=$(=Max(Date))"},

Year = ,

FiscalYear = ,

Quarter = ,

Week = ,

[Period (#)] = ,

Month = P({<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'}>} Month),

[Brand Code]={'=(365-Max( $(vSetRolling12) Date) + Min( $(vSetRolling12) Date)) <$(vL4LNrDays) and Sum({$(vRolling12)} _Inactive_Brand)<1'}

[#Turnover EUR])

Re: Set Analysis restrict months depending on other expression

bruno_m_santos — Thu, 29 Aug 2013 10:53:17 GMT

Unfortunately it does not work.

If I have a filtered brand works because P () returns only the months for that brand. Otherwise, it always returns every months. It means that I've brands that have worked the all year and others not.

Thanks

Bruno

Re: Set Analysis restrict months depending on other expression

whiteline — Thu, 29 Aug 2013 11:26:59 GMT

You can just clear [brand]= in the set expression for P() or use full {1} set instead of current {$} as a base.

Re: Set Analysis restrict months depending on other expression

bruno_m_santos — Thu, 29 Aug 2013 12:07:47 GMT

I may be saying a big nonsense...

But with espression P (), is it possible to evaluate by each line (Brands), which were the months with data?

If I put {1},it 'll be disrargind the filters, but don't make diference. The fundamental issue is that, by each line (Brand) it's need to evaluate if exists data on expression2.

Bruno

Re: Set Analysis restrict months depending on other expression

whiteline — Thu, 29 Aug 2013 14:10:03 GMT

In such a case to use set analysis you should create some complex key field, and use it for filtering.

For example, the field constructed of Brands and Months allow you select each combination of brand and month separately.