I have now successfully incorporated that table into my datamodel: It is linked via the field "turnover_type" to a dimension table and when I clear all selections in my diagram, both turnover types are displayed.
I have also integrated the fixed_costs into my drilldown_group: On the second level down (the first is the turnover_type), I have a formula depending on the selected turnover_type: The field holding the next subcategories has a different name as the field holding the names of the individual fixed_cost_items.
The next-lower levels are unchanged, but they do not apply to the fixed costs and that seems to be ok - when I select an item within the fixed costs, nothing more happens, it is just selected and the others disappear.
My remaining problems for the moment are the following:
- As I have mentioned, as soon as the user has selected a date, that uppermost level is skipped (so to say, the turnover_type "variable" is automatically selected) and QlikView just displays the next-lower level within that turnover_type. Can I change that?
- I cannot yet seem to get the formula for the turnover right: Depending on the selected turnover type, there should be a "real" formula calculating turnover or that column should just display the corresp. field from my fixed_cost_table. That part works. That does not, however, account for the uppermost level where there is not yet any selection on that field - on that level, currently nothing is displayed. I guess I need a rather complex conditional formula here to say (pseudo-code):
- When there is no selection at all yet, display
- a formula for the variable turnover (in the corresp. row)
- the content of a field for the fixed costs (again in the corresp. row)
- As soon as a selection is made, it gets easier:
- When the selected turnover_type is "variable", always use the formula for the remaining levels of the drilldown_dimension
- When the selected turnover_type is "fix", display the individual items with their costs, there are no more levels to drill into the data.
One possible thing that comes to my mind is the following:
- The formula calculating the turnover (variable) uses fields from the dimension table to which my fixed_costs_table is linked.
=> I could incorporate that formula on scripting level so it is a readymade field that is just displayed.
=> I might be able to adapt my fixed_costs_table (the nr. of fields) so that I can concatenate it to that dimension table. I could then put the costs (fixed_costs) in the same field where the calculated turnover (variable) is, so my "formula" would be the same for both rows.
O_O - I just noticed that now the lower levels of the drilldown_dimension within the variable part have also been blocked now: I can go no lower than the second "from the top".
I think something is beyond my understanding here - currently, it seems to be getting better in one place and simultaneously worse in another...
Well - it seems to be working, but I am unsure about this: To make things work with an automated bookmark and to be able to display both turnover_types on the uppermost level and to be able to use the same drilldown_group, I had to adapt the names of some fields - but now I have the fixed_cost_table linked to my dimension table by a synthetic key made up of four fields... Only one of the fields matches content-wise, though.
I think I have found a way now. I cannot delete this entire thread or I would probably do so.
I linked the new Excel table to my dimension table, so the problem with the Date remains: When the user selects a date first, the "turnover_type" level is skipped since there is no sensible selection possibility there. I just added a trigger to the textbox displaying the selected date to remove this selection again.
I have renamed a few fields in the new table, so now it is linked to the dimension table by a synthetic key made up of three fields, but only one matches content-wise, the other two I just had to rename to enable a selection and to be able to use the same drilldown group for both.
I hope this little monologue eventually helps someone facing the same problem.