Set Analysis restrict months depending on other expression

[bruno_m_santos]

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

[whiteline]

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

[bruno_m_santos]

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

[whiteline]

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

[bruno_m_santos]

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

[whiteline]

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.