I have a general problem with my calendars, I have different facts tables and for each one I created a calendar. The facts tables are connected to a calendar using the field date. But when I try to make a line chart using: days, months, year as a dimension and count the ids of the table as an expression I get a null date plotted. The expression value is correct in the chart but it seems it cant find a day, month or year. So, I tried deleting the key field date and I linked the tables using multiple fields: day, month, and year. This created a synthetic key, but finally plotted the info as I wanted. So I dont really know what the problem is. I want to get rid of the synthetic tables. Any ideas?
I would stick to the date as key. It seems to me that not all your records in the fact table have a valid key set.
So first step would be to look into how your key fields is derived from other fact table fields (you said, it works using day, month, year, so I assume these are the original calendar fields in your fact table, not date?), and to double check that each fact table record has a valid key field.
You would probably need to post more details if you want someone to help you with your issue, best by creating a small sample QVW file that demonstrate your issue.
I think I found the problem, but I dont know how to fix it. The Calendar table has adittional lines with null values for the fields year, day, month, weekday an quarter. I know for sure that the calendar is calculating the dates right and it should only have 6 lines (green selection). I dont know why the extra lines (red) are appearing in my calendar table. So when I try to make a line chart, the dimension displays the null values from these extra lines. I attached the data from the 2 tables in an excel file. I am using the date as a key field.
Your date field is really a timestamp. That's why you get the 'extra' records. In each of those records the date also has a time part. Your calendar creates the dates without the time parts and your other table has the dates with time parts. Because they use the same field name (fecha_click) it looks like the calendar has extra records, but those are simply the timestamps from your other table. You can use the floor function first on fecha_click to remove the time part:
