Static Data in Straight Table

[maahivee]

hello Qlikview Expert,

I have a complex requirement here.. I wanted to show the no.of picks/tasks done on each day..

So say i have 1100 Open picks in the beginning of the day and at 8AM 25 were completed. so it would be 1075 and at 9AM 100 completed it would be 975 and at 10AM 75 were completed but 400 were newly added to open it would be 1300 as below..

Time	# of Picks	Added Picks	Completed Picks
Begin	1100
8:00 AM	1075		25
9:00 AM	975		100
10:00 AM	1300	400	75

My question here is as the numbers keeps changing every hour in the database and i reload the dashboard every hour the numbers in QV changes too.. I mean the numbers where it should show 1100 at the start point it is now showing 1300 since that is the current open number in the database.. P.S: when a task is completed the record is deleted from open and put to completed..

Is there a way I can display as in the above table.. to show the numbers in that particular time frame cell even if its deleted from the database table.. kind of like a static number for that day and should change next day with the new number for that day.

Please note we have task creation date and task end date with time in it.

The point with using Previous() function is that, we reload every hour and it changes every hour with previous hour data not the beginning of the day data.. But when we move on to the next day it should display the start point for that day not the previous day.

Thank you guys in advance..

[swuehl]

As far as I understood, you main task / question is how to set up a ETL process that captures every task from the source DB and stores the historic data?

I believe if you get to the point having access to this granularity and looking at the complete record set, you should be able to set up the open / created / closed counts quite easily.

Or have I misunderstood what you are trying to do?

[maahivee]

‌hey Swuehl, thank you for the reply..... I already have a data store In which I store data and do refreshes on..

i also have access to such granular level.. My point here if I can achieve the result in a straight table as of the example table given above based on task creation date, task end date,(which has time on it) and no of tasks columns.. My point is can I capture the data as a static value in a straight table or by any other ways in QV either in script or front end?? what I want to capture here is the no of open tasks at the beginning of each day.. And why is it so hard is coz the open tasks status changes once it's closed..

SO is there any way I could capture the open task number at the beginning of each day and do rest of the calculations based on it....

[swuehl]

Don't use the status field to decide if the task is open an a given date, use the start and end dates.

You can create reference dates for each interval, then use the reference date as timeline to count your distinct tasks (which will be the open tasks per reference date):

Creating Reference Dates for Intervals