Set Analysis Default Filtering

[whodisturbsmyslumber]

Hi All - I can't seem to find a solution to this question and I can barely describe it words so thank you for reading through. This is a simplified version of my actual data set but ultimately creates the same issue. Say I have 2 tables: first table is a monthly record of a manager's name, title, and team size. Second table has monthly records with manager's name, title, and accounts sold.

I want to be able to select a Manager, and select a month in 2019 and have a side by side view of their performance in the month I selected and then the same month in 2018. Performance is defined as total accounts sold / team size. Sample data set is attached and this is the calculation I'm using to get the 2018 value. The 2019 value works fine.

If I select Barbara and 201903 as my month, I won't get any data for 201803 because Barbara changed Job titles in 2019. Even though I didn't do any filtering on job title, Senior Manager is Barbara's only job title in 2019 so it gets applied as a filter to 2018.

Are there any ways to work around this? I also want to be able to cut the data by job title, so I can't just get rid of that field. Also, restructuring the underlying data isn't an option. Is there any way to create the set analysis differently where it doesn't apply the filter to job title for all time?

Hopefully I've described this issue in a way that makes sense. Thanks for reading!

=
sum(
{<T2_Month= {">=$(=min(T2_Month)-100)<=$(=max(T2_Month)-100)"} }
T2_AccountsSold) /
sum(
{1<
T1_Month= {">=$(=min(T2_Month)-100)<=$(=max(T2_Month)-100)"},
T1_TeamLead=P(T2_TeamLead),
T1_JobTitle=P(T2_JobTitle)>}
T1_TeamSize)

Thank You!

[ajaykakkar93]

Hi,
I'll take a look

[ajaykakkar93]

why min(T2_Month)-100 & max(T2_Month)-100?
-100 ??
do you want previous year of your selection i.e. 201901 to 201801

[ajaykakkar93]

is this what you need?

[ajaykakkar93]

Here is the attached variable name in image 

Variable : 

1) vMinT2Month  :=date(addyears(min(Team2Month),-1),'YYYYMM')

2) vMaxT2Month : =date(addyears(max(Team2Month),-1),'YYYYMM')

------------------------------------------------------------------------------------------------

Load script :

Team1:
LOAD
T1_TeamLead,
T1_JobTitle,
date(date#(T1_Month,'YYYYMM'),'YYYYMM') as Team1Month,
T1_TeamSize
FROM [lib://App/QS Data Example.xlsx]
(ooxml, embedded labels, table is Sheet1);

Team2:
LOAD
T2_TeamLead,
T2_JobTitle,
date(date#(T2_Month,'YYYYMM'),'YYYYMM') as Team2Month,
T2_AccountsSold
FROM [lib://App/QS Data Example.xlsx]
(ooxml, embedded labels, table is Sheet1);

------------------------------------------------------------------------------------------------

Measure : 

sum(
{<Team2Month={">=$(vMinT2Month)<=$(vMaxT2Month)"}>}
T2_AccountsSold)
/
sum(
{<Team1Month={">=$(vMinT2Month)<=$(vMaxT2Month)"},T1_TeamLead=P(T2_TeamLead),T1_JobTitle=P(T2_JobTitle)>}
T1_TeamSize)

[ajaykakkar93]

hi,

In attached application refer sheet "Set Analysis Default Filtering" hope you requirement is solved

Note : i have used the same method i have mentioned in my previous reply.