I am just trying to fix something in an app built by a colleague. The issue I am facing is the following:
- There is a rather a big datamodel with a lot of tables in it (it includes a datamodel loaded BINARY).
- One table holds data about shipped packages up to today - logically it cannot show anything in the future.
- The current requirement is just this - to show Forecast-data until the EOM.
- My colleague has created a small Excel file with the data to be displayed
- So far, that is easy - I have created an additional field in the table for that data and then concatenated that file => Now all the dates until EOM are there, just those in the future have no data except this one field.
- It works when I deactivate all but these two tables.
<=> As soon as the other tables are loaded again, it stops working - the diagram does need some fields from other tables, but not very much, but then it shows only the data up to today.
That table still holds those values, I can see them in a straight_table I have made for the purpose - but the chart only shows data up to today.
Providing data will be difficult as it always is with big datamodels - but I can tell you all you need.
- The dimension of this combo_chart is a calculated one:
=> The field to be used as dimension is in this table (where I have added all the dates until EOM)
=> It is limited by an IF_condition on a value in a second table.
- There are two formulas in this diagram
=> One is a value from a 3rd table (with a set_expression querying a value from those 2nd and 3rd tables)
=> One is a value that exists in that 1st table (and which also exists for the dates in the future) => That 2nd value should be displayed for all the dates in the future.
=> Even if I deactivate the 1st expression (that contains that set_expression), the values are shown only up to today.
Can you tell me any possible reasons for this?
(I have proposed just making a small new app with only this table and the necessary fields from the others, I will try that myself)
maybe you need some more fields within the concatenated future-table to ensure that further links will work properly:
....
concatenate
Load Date, Store, Artikel, Value From x;
and used Dummy-Values as link to other dimension-tables. Or you divide the future-data into a deeper granularity. But first check if your date-field from the futures has the same format as from you shipping-data.
I have now done what I thought easiest - singled out just that table into a new app. I'm now going to try and add just those fields from other tables that I need to make that diagram work.
Funny, though, when I link to my mastercalendar via the date and select the current year and month in the calendar, then that other table again displays only the dates up to today - though the calendar runs up to the end of next year ...
P.S.: The link itself does no harm.
When I just generate a year and month out of the date_field I have in that other table, that is fine - I can select 2015 and March and I am shown all the dates up to the EOM.
maybe the date isn't really a date else a timestamp with invisible times - try it with floor(date) as date - and/or the formatting is wrong and you need something like date(date#(date, 'Format'), 'Format') as date.
there is a timestamp, but the field I wanted to use to link to the master_calendar was already derived with Floor(). The format could be an issue. I'll try that.
However, it works now. I'm not quite happy with the fact that I have two tables in my datamodel now with an N:N relationship - in one of the tables, one field should actually be unique, but it isn't for some reason - I've had that before and elsewhere, too. I guess it's a different problem that I cannot solve on the design_side, but I'll get back to that when I have the time.
