Hi
Here is one solution. This one calculates the values of 'days since merchandise was last in stock' using script, allowing you to load results as data values rather than calculating them on the fly in a chart.
Based on your results table you only need to load the DATA and Entrada fields. I renamed the DATA field as STOCKDATE for clarity and named the table 'Raw'.
STEP 1:
The first step it to ensure that your data is loaded in ascending order by the STOCKDATE. Depending upon how you load your data you can either ensure the data is sorted before loading or you can use the ORDER BY function in the load script to sort the data as it loads.
STEP 2:
The second step involves identifying the first date in your list of STOCKDATES and assigning this value to a variable:
LET varMINDATE = Num(PEEK('STOCKDATE',0,'Raw'));
STEP 3:
The third step involves finding the dates on which merchandise was in stock.
QUALIFY*;
UNQUALIFY STOCKDATE;
STOCKDATES:
Load *
Resident Raw
WHERE Entrada>0
Order By STOCKDATE ASC;
UNQUALIFY *;
This script uses a 'WHERE' clause to only load data for dates on which merchandise was in stock and then sorts that data in ascending order by STOCKDATE. The table, which I called STOCKDATES, is qualified to avoid any conflict with the Raw table.
STEP 4:
The fourth step uses the PREVIOUS function to help calculate the number of days between the days on which merchandise was in stock. It then uses a left join to attach that value to the relevant value of STOCKDATE in the Raw table.
Left join (Raw)
Load
STOCKDATE,
If(IsNull(Previous(STOCKDATE)),
num(STOCKDATE)-num($(varMINDATE))+1,
STOCKDATE-Previous(STOCKDATE)) as DAYS_SINCE_LAST
Resident STOCKDATES;
When there is no stock on the first date the Previous function will return a null value. You can use this along with the variable varMINDATE to determine the number of days since the start of your period. As there was no stock on the first day you have to add 1 to the calculated value to arrive at the first value of days without stock i.e. 13 days in your example.
STEP 5:
The final step just involves releasing the memory used by the STOCKDATES table by dropping it:
Drop Table STOCKDATES;
Complete Script:
So the full script, including your table as an inline loaded table for this example is:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Raw:
LOAD * INLINE [
STOCKDATE, Entrada
01/01/2012, 0
02/01/2012, 0
03/01/2012, 0
04/01/2012, 0
05/01/2012, 0
06/01/2012, 0
07/01/2012, 0
08/01/2012, 0
09/01/2012, 0
10/01/2012, 0
11/01/2012, 0
12/01/2012, 0
13/01/2012, 1
14/01/2012, 0
15/01/2012, 0
16/01/2012, 0
17/01/2012, 0
18/01/2012, 0
19/01/2012, 1
20/01/2012, 0
21/01/2012, 0
22/01/2012, 0
23/01/2012, 1
]
;
LET varMINDATE = Num(PEEK('STOCKDATE',0,'Raw'));
QUALIFY *;
UNQUALIFY STOCKDATE;
STOCKDATES:
Load *
Resident Raw
WHERE Entrada>0
Order By STOCKDATE ASC;
UNQUALIFY *;
Left join (Raw)
Load
STOCKDATE,
If(IsNull(Previous(STOCKDATE)),
num(STOCKDATE)-num($(varMINDATE))+1,
num(STOCKDATE)-num(Previous(STOCKDATE))) as DAYS_SINCE_LAST
Resident STOCKDATES;
Drop Table STOCKDATES;
To see the results simply create a TableBox with the dimensions STOCKDATE, Entrada, DAYS_SINCE_LAST
E.G.
| STOCKDATE | Entrada | DAYS_SINCE_LAST |
| 01/01/2012 | 0 | |
| 02/01/2012 | 0 | |
| 03/01/2012 | 0 | |
| 04/01/2012 | 0 | |
| 05/01/2012 | 0 | |
| 06/01/2012 | 0 | |
| 07/01/2012 | 0 | |
| 08/01/2012 | 0 | |
| 09/01/2012 | 0 | |
| 10/01/2012 | 0 | |
| 11/01/2012 | 0 | |
| 12/01/2012 | 0 | |
| 13/01/2012 | 1 | 13 |
| 14/01/2012 | 0 | |
| 15/01/2012 | 0 | |
| 16/01/2012 | 0 | |
| 17/01/2012 | 0 | |
| 18/01/2012 | 0 | |
| 19/01/2012 | 1 | 6 |
| 20/01/2012 | 0 | |
| 21/01/2012 | 0 | |
| 22/01/2012 | 0 | |
| 23/01/2012 | 1 | 4 |
I hope that helps you to achieve your aim.
Kind regards
Steve
