Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
PROBLEM: Since adjustment data does not have a "KEY", it cannot be logically joined to the regular "tables"
In my header, we have Customer#, Vendor# and Sales# as well as OrderNo but Invoice date only post the Month and Year, not the day as well as the table for the adjustments.
If I join on these items, I get a loop because of the Salesperson table is not part of the header. Our orders can have multiple salespeople with an allocated percent of the sale.
If I join the Salesperson to the OrderHeader, my calculations go way out of wack and my application no longer balances, but I can join in the adjustment data although it creates a SYNTH Key
The scripts and app is quite lengthy and convoluted, so I am not sure the best way to share - hopefully someone out here understands my dilemma.
I have attached a table diagram
I think this is a case for a Link Table with slowly changing dimensions.
Am I correct in assuming that your set-up is comparable to the combination of Sales Data (daily) with Plan information that is aggregatesd across all divisions, products and regions (so no detail level) and runs for MonthYear periods only?
Kind of - we tally sales by customer, vendor and salesperson. I can aggregate sales data directly from the invoice data. We do international import and export.
As an example of an adjustment, we need to cost our purchases and calculate sales margins, so when we invoice we will put down an estimated cost for shipping or wharfage, customs etc. The overseas supplier may not ship for 3 months and rates/fees may change or the bill may come in 1-2 months after the invoice. Once we reconcile the costs, they get applied to the months sales as a straight Margin adjustment.
So yes, these adjustments come in the MMYY timeframe, but do get applied to the original salesperson/vendor/customer
I'll take a look at this tomorrow
http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/03/slowly-changing-dimensions