topic Re: Canonical Calendar (multiple dates) Issue in QlikView

Canonical Calendar (multiple dates) Issue

Anonymous — Mon, 15 Jun 2015 16:59:43 GMT

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:

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

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:

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.

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

Thanks a ton,

Ron

Re: Canonical Calendar (multiple dates) Issue

ramoncova06 — Mon, 15 Jun 2015 21:01:16 GMT

your salesrep table is linked by state, this join is too broad and will most likely not interact with any date changes, are you dropping the sales order field from that table for a specific reason ?

Re: Canonical Calendar (multiple dates) Issue

Anonymous — Mon, 15 Jun 2015 21:31:18 GMT

The sales reps are assigned by state, so i'm connecting them in by the customer's state. This gives the proper result. I actually fixed the date pivot issue with the PartsRevenue column with a concatenated key (SoldTo and date(PostingDate) as Key). Now, the problem is getting accurate results for the PartsRevenue column. The new/used vehicles column is accurate. Here's a working data model with an improper calculation for PartsRevenue: