Assign values from one level to next

[Karunya]

Hey Guys,

I am facing the below issue:

I have a table as shown below 

Unit	Measure	YearWeek	Employee	ValueType	Sum(Value)
100-1	Calls	2019-29	AA	Actuals	30
100-1	Calls	2019-29	AB	Actuals	20
100-1	Calls	2019-29	AC	Actuals	30
100-1	Calls	2019-29	-	Target	100

I have targets available at Unit level, which now have to be assigned to employees in that Unit.

Is there a way to do this using set analysis? 

Alternatively, is there a way to do the same using data load editor

Thanks in advance!

-Karunya

[sunny_talwar]

Assign it how? Is there a logic? Based on the weights of Value?

[Karunya]

Hey Sunny,

Yes.

The targets value shown per unit are per employee/week.

Eg: Considering the same table i shared:

Unit	Measure	YearWeek	Employee	ValueType	Sum(Value)
100-1	Calls	2019-29	AA	Actuals	30
100-1	Calls	2019-29	AB	Actuals	20
100-1	Calls	2019-29	AC	Actuals	30
100-1	Calls	2019-29	-	Target	100

Target is 100 for unit 100-1 implies, each employee under Unit 100-1 (AA, AB, AC) have target of 100 per week

Hope this makes it clearer.

[sunny_talwar]

But how do you want to display the final output? I am guessing the table you have shared is how the data looks like, is that right?

[Karunya]

Right.

Its more like I am creating a master table, which can be used in multiple ways.

So the options i have are,

1) have it modified in data load editor, wherin I have to assign value based on pre set conditions

2) Set analysis, which I have to use  at the time of report building

[sunny_talwar]

2 options listed make sense, but how is the final table need to look like?

[Karunya]

The final table needs to look like below:

unit	Measure	Year Week	Employee	Value Type	Sum(Value)
100-1	Calls	2019-29	AA	Actual	30
100-1	Calls	2019-29	AB	Actual	20
100-1	Calls	2019-29	AC	Actual	30
100-1	Calls	2019-29	AA	Target	100
100-1	Calls	2019-29	AB	Target	100
100-1	Calls	2019-29	AC	Target	100

So basically the target per unit is now assigned to each employee under that unit.

[sunny_talwar]

I don't think this can be done directly on the front end... but if you want this, it can be done

unit	Measure	Year Week	Employee	Value Type	Actual	Forecast
100-1	Calls	2019-29	AA	Actual	30	100
100-1	Calls	2019-29	AB	Actual	20	100
100-1	Calls	2019-29	AC	Actual	30	100

[Karunya]

This wouldnt work, because I built the report based on the master table. Now the new requirement is to have targets at one level more i.e. Employee. so changing master table structure means changing the report entirely.

Rather i was also thinking in line of creating another table with the required fields, using joins. This would help me to refer to another table in set analysis formula. But this definitely not the best way to design a solution i feel.

Is there a way using set analysis to superimpose the targets at unit level to corresponding employees?

[Karunya]

out of curiosity, how would the below table be done using set analysis? 

unit	Measure	Year Week	Employee	Value Type	Actual	Forecast
100-1	Calls	2019-29	AA	Actual	30	100
100-1	Calls	2019-29	AB	Actual	20	100
100-1	Calls	2019-29	AC	Actual	30	100