Relationship Anomoly?

[#1]

I am new to Qlikview and still practising my skills. I decided to apply Qlikview to Microsofts Northwind database for practise purposes. Amongst other tables I created an Order and OrderDetails Table - each with an added calculated column OrderValue and LineTotal respectively. (Original - I know!). I created a pivot table of with Customer and Year as Dimensions and (initially) Sum(LineTotals) and later Sum(OrderValue). In both cases the pivot table displayed most of the rows exactly as expected but for some customers it created a blank year column and placed values in there. More investigation showed that these were the values from one or more Orders from the last year (1994) even though the Orders clearly had a 1994 orderdate. Any clues as to what is going on here? All the Order Details have corresponding Orders and v.v.

[#2]

Can you post your QVD? It may be easier to troubleshoot if we can take a look at what you're doing.

EDIT: Yeah, sorry, I meant the QVW. Thanks Oleg!

[#3]

My version of Qlikview is currently Personal edition and it does not appear to let me export a QVD for the application but I have exported the individual tables as QVDs. Unfortunately this site tells me that .qvd and .qvw files are not allowed when I try to attach them.

[Oleg_Troyansky]

It's the QVW that we need, not the QVD (I believe NMiller had a typo...)

You should be able to upload a QVW, everyone does. Open the "Options" tab and click on "Add/Update" button on top.

[#5]

Thanks. Attached.

[#6]

I have to assume that the anomoly has something to do with the OrderDate. I attach an analysis which shows that all the orders whose OrderValues get pushed into the blank year column have OrderDates between 24/03/1994 and 31/03/1994 inclusive. If a customer has 2 orders in that date range they are summed up in the blank year column. Any that lie outside that range are summed up within their years exactly as expected. I am still struggling to make sense of this.

[#7]

I think it has something to do with your temp calendar. If, in your Customer Annual Sales chart, you replace the Year dimension with Year(OrderDate), you get rid of the blank year.

Could you try to reload the data without dropping the TempCalendar table? I'm guessing there is something happening there so that the dates aren't appearing in the MasterCalendar.

If you look at the value of your vMaxDate variable, it is set to 23/03/1994. Your TempCalendar is only generating through that date, which means there are no dates in the Master Calendar that match up with the missing dates. You may even be having a problem with date format since only 24/04/1994 and up isn't appearing.

EDIT: Check out the main tab of your load. You have:

SET DateFormat='DD/MM/YYYY';

Modify that for the date format you are using.

[Oleg_Troyansky]

The problem, as I see it, is that your Master Calendar is missing 5 dates, and those seem to be your problematic dates:

OrderDate	Year	Month
24/03/1994
25/03/1994
28/03/1994
29/03/1994
30/03/1994

OrderDate	Year	Month
24/03/1994
25/03/1994
28/03/1994
29/03/1994
30/03/1994

Check your logic of generating the Master Calendar

[#9]

Thank you to both yourself and Oleg for your reponses. I have "hard-coded" the max and min dates which eliminated the problem. It does beg the question as to why the pieces of code:

LET

LET

Did not appear to pick up the Lowest and Highest OrderDates - or at least the Highest one.

varMaxDate = Num(Peek('Orders.OrderDate',-1,'Orders')); varMinDate = Num(Peek('Orders.OrderDate',0,'Orders'));

[#10]

Sorry - let me try again this time using the suggestion to copy in via Word:

LET varMinDate = Num(Peek('Orders.OrderDate',0,'Orders'));

LET varMaxDate = Num(Peek('Orders.OrderDate',-1,'Orders'));