I have a problem cannot solve and attached a little example.
Data:
3 dimensions : Day, Salesman, Customer (Salesman, Calendar, Custome sheets in xls file)
1 Fact: it refers to all dimension and has 1 measure Price (Data Sell sheet in xls file)
So, the model is very simple.
I want to select one or more salemans (let's say Salesman1) and get:
- the list of all days Salesman1 worked (partial result in Table 1 of my example)
- the list of all Salesmens who worked on same days Salesman1 did (partial result in Table 2 of my example, where there is only a concatenation)
- put Salesman(s) selected, all days Salesman(s) selected worked, all Salesmens who worked on same days the selected Salesmen did in the same cross table
I could get Table 3-1 or Table 3-2 as a partial result but what is missing is the first dimension (Salesman selected).
Actually I want a result like in table 3-2 but adding as first column the list af selected Salesman(s).
I simply tried to add Salesman as dimension but it does not work (see table 4).
So, for example, if I select Salesman1, I would like to see a cross table with:
- 1st colum: Salesman1
- 2nd column: all Salesmen who worked same days Salesman1 did
- 1st row: all days Salesman1 worked (1,5,10,13,20,23,27)
- cell expression: a cross if Saleman actually worked on a day
but I cannot solve thi problem using set analysis.
Jus as a quick and dirty workaround I created another fact table: actully I doubled the fact table. But this solution is not the best solution because in my real application Fact table has millions of records
