Complex calculations - break them down using auxiliary calculations

    This is about making life easier for you by making calculations on the GUI of a QlikView application easier to understand at any time (4 weeks after you made it, 1 year after ...) both for you and for anyone else who might have to deal with your applications.

     

    Sometimes you have to do a complex calculation like the following:

     

    - We have a delivery of 14 items (there is only one, so this is all we have to work on)

    - A nr. of items (say, 10) is due at a specific date (DueDate1)

    - A nr. of items (say, 5) is due at another date (say, DueDate2 is one week after DueDate1)

    - A nr. of items (say, 5) is due at another date (say, DueDate3 is one week after DueDate2)

     

    => In week1 (runup to DueDate1, we have 14 items, so we can process the entire stock of orders (10)

       => We have 0 orders to carry over to week2

       => We have 4 items to carry over to week2

    => In week2, we have 5 new orders + 0 leftover_orders, but only 4 items left

    => We can only process 4

       => We have 1 order to carry over to week3

       => There are 0 items left to process in week3

    => In week3, we have 5 new orders + 5 leftover_orders, so that's 10 orders in total

    => We have 0 items left, so we can process 0

     

    That makes for a tremendously complex calculation - if you want to do it all in one go.

    There is, however, another way: Go about it in small steps like they do in school.

    - In almost any kind of  diagram (this will be a straight_table), you can refer to calculations that are further up in the dialog
      (calculated first) just by naming them in a "later" calculation - you just use their result without repeating all the calculation

    - In a straight_table, you can choose which columns to display and which to hide

    => Voila - combine the two possibilities and you see you can calculate both the leftover_orders and the leftover_material from every
          week and put them in columns that you just don't display and you can still refer to them by just naming them in "later"
          calculations

    => That makes the entire calculation much easier to understand.
          If you want, you can write the prefix "aux" or sth. in the formula_name to show immediately that it is an auxiliary calculation and thus not displayed.

     

    I attach a small sample Excel file and the corresponding .qvw to illustrate this.