Dimension Date Extrapolation

[#1]

I'm tracking a project that has a defined start date and target end date. There is also a target amount of work to be done throughout the project. Weekly it's recorded how much total work has been completed in the project. I can easily graph for each week the current total amount of work done. Also in the same chart I graph the ideal target total amount of work done by calculating how much work should have been done by the current week assuming each week an equal amount of work is done.

What I need to do is somehow extrapolate the target amount of work from project start to end, show this line on the graph and then for the weeks that have data, show the current progress. How might I extrapolate the date dimension into the future to the end date?

[#2]

I'm not entirely sure I understand what you're describing, but we can talk generally about how to work with dates.

Dates in QlikView are stored as numbers. Who whole portion of the number is a number of days, and the fractional part of the number represents fractions of days.

So, if you have a date; say Jan 14 2010, and you want to add 10 days to it, just add 10. If you want to add 8 hours, add 8/24. (there are 24 hours in a day)

From there, it's a matter of problem solving to get to the answer you're looking for. Unfortunately, I don't understand the problem (a key statement seems to be missing from your post) so I can't really make any suggestions.

I'm not sure exactly what you're attempting to extrapolate.

[#3]

OK, lets say I have a start date 1 and an end date 100. I have 100 units of work to do, so If an equal amount of work is done every day I'd do 1 unit of work. Each day the actual amount of work is recorded and a total work completed accumulated. After 10 days I've recorded actual work up to day 10. I create a simple graph that shows the cumulative work completed for each day. However I also want to show a line for the ideal amount of cumulative that should hve been done by this day to see if I'm tracking to be done on time. This is easy, I just take my total 100 units of work divide by the number of days and then multiply by the current day. Now here's the trick, what I want to do is to extend the graph to continue the ideal line all the way to the end date. However since I don't have data yet for the days past day 10, QV only shows the 10 days worth of data. How can I extend my ideal line all the way out to day 100?

[#4 · Accepted solution]

Ah! Now I get it.

I believe what you need to do is actually create the days what will later be filled in with the data. That way, the totals for those days will all be zero, but you can add in your extrapolations for those dates in your graph.

The approach is typically referred to as a 'master calendar'; the idea is to basically create another table that contains all possible dates that link to your other object. So, you would generate all of the days between 1 and 100 in a separate table, link it by date field to your existing object, and use the dates from the new 'master calendar' table as the dimensions in your graph.

Trying to scrape up an example here… there have to be some on the forums, or samples. Here's a really basic example that will get you started:

http://community.qlik.com/forums/t/26079.aspx

[#5]

Thanks for the info and link. This seems like what I need to to, generate the dates on a weekly basis. I'll give it a shot and see if ti works.

[#6]

This seems to generate the Calendar I need:

LOAD

date

(date#(20100509,'YYYYMMDD') + (recno() * 7),'MM/DD/YY') as

"start_of_week"

AUTOGENERATE

(MakeDate(2011, 4, 21) - MakeDate(2010,5, 9) + 1)/7;

[#7]

One more thing I need now. For the dates that I don't have data for yet (as they're in the future), what I need to do is use the value of the last date I do have data for as the value. So somehow use the last available value for a field to project into the future dates. How might I accomplish this?

[#8]

Is this value the 'accumulated work' value that you described previously?

I would expect that QV should display the accumulated total still for the periods with no data, assuming you're using the 'accumulated total' option in the properties of the expression. I haven't tried it though explicitly to know.

Or is it some other data point you're trying to use the 'last' value from?

[#9]

It is the accumlated work, but QlikView isn't doing the accumulation. The data that is pulled from the database is already the accumulated total. It similar to the QV option, but instead having QlikView do the accumulation I just want it to use the last value in the absense of future data.

[#10]

I was able to use some conditional logic in the expressions to do what I wanted. Thanks for your help.