I’m trying to fix an issue with dates and tried as much as I could but still not able to fix the issue. So I’m posting it here hoping someone will help me. As this is a data model issue it might take some time for you guys to help me with the issue so please help me in identifying the issue.
Basically, I’m trying to join a budget data table with another major “sales” fact table as I need to create a report with combination of fields from these 2 tables. As these 2 tables are not at the same granularity and neither of the tables have the date field that I need. I used 2 other calendar_dim and fiscal_dimension tables from same schema, joined these 2 tables got all the date related fields. Finally joined this date (Fiscal)table to main fact “sales” using the calendar_date_id.
Now the issue is when I create the report the data is not associated with the right time dimensions
Instead of 2016 Fiscal (2015 half and 2016 half calendar year) year, data is associated to 2014 Q3 which is not correct. So please can someone point me out what’s wrong with my code? Thank you,
I have attached the sample qvw and also the excel files with data.
I use in a similar case a different approach and just concatenate sales- and budget-tables whereby I create per makedate(Year, Month, 1) a date which resolved the different granularity on the date-level. This method is quite easy and performed fast within the script and the UI.
Thank you i will try that but actually i need to use both Fiscal and Calendar Year/Date/Month on the app UI over multiple tabs so i'm not sure if that works for all.
Thank you, yes that's why concatenated the tables and used applymap to bring in the date_id to the budget table but it's not working the way it was suppose to work. if you get a chance please can you have a look at the attached qvw. Thank you,
Thank you for the response. I have attached the qvw (Dates_issue.qvw) and also the excels (data). please let me know if you still cannot see the attachment. Thank you,
You need to create a date within the budget-load with something like: makedate(Year, Month, 1) and this date-field get the same name like this from the sales-load and mustn't be mapped to the calendars else there should be a normal table-association work.
Thank you again for the response.
Here is what i'm trying to do please let me know if that makes sense
I'm trying to join /concatenate 'sales' and 'budget' table, both tables have time dimensions(year and month) but Sales table has both Fiscal and calendar year and month and Budget table has only calendar year and month and none of these tables have date (day ). I'm trying to build a kind of master calendar table using 2 other calendar tables('calendar' table and 'fiscal calendar' table). These 2 calendar tables have all the time dimensions including 'Date'. So i joined the 2 calendar tables named it as "Fiscal". Then joined this 'Fiscal' table to 'Sales' using a key field 'calendar_id.' As budget table doesn't have calendar_id, used applymap to bring in calendar_id to budget table. does that makes sense? Really appreciate your help. Thank you,
I think your tables respectively load-structure should look like this (here in a simplified manner):
load ID1, ID2, Date, Sales from Sales;
load ID1, ID3, Date, Budget from Budget;
load * from MasterCalendar;
load * from FiscalCalendar
whereby the association between the fact- and the dimension-tables is Date. If there is no date-field within Sales and/or Budget you need to create them from the year- and month-fields.