i have a sales table already loaded in QlikView (similar to what i have below).
Each sale is done using an "instrument Type" which is allocated at customer level.
So for example the customer A has just one instrument allocated (revenue by instrument is equal to 100), on the other hand customer B has 2 instruments allocates, so the revenue by instrument is equal to 75 (150/2)).
The goal is to evaluate the profitability of each istrument allocated, so for customer A and Type 9 the profitability is 100 $, for customer B profitability by instrument is 75.
To do this i have to load a table where i have to specify, for the combination of customer and instrumenttype which was the allocation of instrument for a certain period (e.g. 2 instrument from 1st till 10th and 4 instruments from 11st till 31st). The allocation change day by day. I would like then to evaluate in a certain month which is the average of instrument allocated, and then divide sales by this value.
Quesiton is, which is the best solution to implement this ? A separate table linked to the sales table?