6 Replies Latest reply: Jun 19, 2015 1:50 PM by Ron Payne RSS

    Canonical date with 2 dates

    Ron Payne

      I've read the QlikDesign Blogs by hic surrounding these topics, and i'm still at a total loss. I've tried this many different ways with an app that has just two dates (invoice created-on date and accounting document posting date). The issue is that invoicing a customer will create an accounting document in SAP, but the actual date when this accounting document is posted (actually effective) can vary. What i'm essentially trying to do is to get a sum of used and new products by customer (SoldTo) and also get the total revenue (Parts Revenue) of each of those customers for a certain area of the business (Parts). The new/used products sum is working and also changing according to selections made in my fiscal calendar (pertains to invoice date). But, i can't get the Parts Revenue to change (pertains to posting date) when making calendar selections. It stays the same... no matter what you select:

       

      fop.PNG


      Is there any front-end expressions that i haven't thought of trying yet? Or, is this strictly a data model issue?

       

      snootymodel.PNG

       

       

       

      SoldTo is the dimension of my chart in the 1st screen snip. DATE field is going to connect my model to the Accounting Fiscal Calendar.

       

       

      This is what the CanonicalCal Table script looks like right now:

       

       

      coocoocal.PNG

       

       

      As you all can see, i dropped fields from both of the main tables to avoid synthetic keys and/or circular references. However, this could be wrong and might be part of my problem.

       

       

      When i make a Year selection, i'd simply like to see the revenue posted for that SoldTo (customer) and the number of cars invoiced to them at the same time.

       

       

       

       

      This issue has plagued me for weeks and any input would be greatly, greatly appreciated.

       

       

       

       

      Thanks a ton,

      Ron