0 Replies Latest reply: Feb 29, 2012 9:31 AM by Jörg Simon RSS

    How to calculate with values over different tables based on criteria

    Jörg Simon

      Hello to everybody,

      I face the following challenge. But maybe it is no challenge for you outside in the large Qlikview Community.I would like to calculate with values of several different tables.

      Based on Orders, Delivery takes place that will be invoiced "as delivered". That is, i.e. 100hours of Consultancy has been ordered, but only 20hours have been delivered in the current month. Those hours are invoiced. Of course only those hours of need be calculated, that correspond to one ProjectNo.

      How to get an exact overview of those hours open (not delivered yet and of course not invoiced yet) and the corresponding expected amount of revenue to invoiced in future on this project?

       

      I tried to use left joins and concatenats but I didn't get through.

      Any ideas are very appreciated.

      Many Thanks

      Josi

       

       

      The tables almost have the same structure:

      Table 1 (Order):

      Field1(OrderNo),Field2(ItemNo),Field3(ItemDescr),Field4(ItemAmount),Field5(ItemCosts),Field6(ProjectNo),MoreFields .....

       

      Table2 (Invoice):

      Field1(InvoiceNo),Field2(ItemNo),Field3(ItemDescr),Field4(ItemAmount),Field5(ItemCosts),Field6(ProjectNo), SomeMoreOtherFields .....

       

      These tables are supplemented by several further tables of the same structure (i.e. if hours needed to be discounted or if hours needed to be delivered again)

      Table 3 (Credits);

      Field1(CreditNo),Field2(ItemNo),Field3(ItemDescr),Field4(ItemAmount),Field5(ItemCosts),Field6(ProjectNo), AgainMoreANDOtherFields...