Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rpayn01215
Creator
Creator

Canonical Calendar (multiple dates) Issue

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.

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

Thanks a ton,

Ron

2 Replies
ramoncova06
Specialist III
Specialist III

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 ?

rpayn01215
Creator
Creator
Author

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:

Capture.PNG