2 Replies Latest reply: May 4, 2013 9:57 AM by Friedrich Hofmann RSS

    Incorporate fixed_costs into a variable_turnover_diagram? Plz help!

    Friedrich Hofmann

      Hi,

       

      I am at a point where I feel I had no proper training yet and I need some serious help with my chart: I don't know whether it's possible to do at all, but here is what I want to do:

      - I have a straight_table_diagram with a drilldown_dimension displaying variable turnover on different levels - the user first selects a date and he can then view turnover figures for that date on different levels.

      - Now I want to incorporate fixed turnover (fixed costs) into the same diagram. The problem is twofold:

           - For obvious reasons, fixed costs cannot be broken down into the same subcategories, so the drilldown_dimension I have will not do.

           - Fixed costs have no connection with the ShipDate that the user can select in that document, they only change once a month or so.

       

      Currently, I am trying to load fixed_costs as a separate table (Excel) and link it via the field "turnover_type" (only that one) to a dimension table - that is itself linked to my central fact table. Things look kind of promising, but not quite like I want it.

      Another thing is, as long as I have no specific date selected, the two turnover_types (variable and fix) are displayed, but it seems that as soon as there is a date selection, QlikView skips this level and goes straight down to the nest level within the variable turnover data.

       

      Can anybody lend me a hand here?

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: Incorporate fixed_costs into a variable_turnover_diagram? Plz help!
          Friedrich Hofmann

          Hi,

           

          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...

           

          P.S.:

          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.

            • Re: Incorporate fixed_costs into a variable_turnover_diagram? Plz help!
              Friedrich Hofmann

              Okay,

               

              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.

              Best regards,

               

              DataNibbler