topic Re: Forecasting problem in QlikView

Forecasting problem

datanibbler — Thu, 12 Mar 2015 10:08:10 GMT

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)

Thanks a lot!

Best regards,

DataNibbler

Re: Forecasting problem

marcus_sommer — Thu, 12 Mar 2015 13:16:02 GMT

Hi DataNibbler,

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.

- Marcus

Re: Forecasting problem

datanibbler — Fri, 13 Mar 2015 11:20:30 GMT

Hi Marcus,

thanks so far!

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.

Re: Forecasting problem

marcus_sommer — Fri, 13 Mar 2015 11:53:27 GMT

Hi DataNibbler,

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.

- Marcus

Re: Forecasting problem

datanibbler — Fri, 13 Mar 2015 13:49:24 GMT

Hi Marcus,

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.

Thanks a lot for now!

Best regards,

DataNibbler