Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
More a general scripting query, rather than a QV specific query.
However it is really wearing me down, any help would be greatly apprciated.
I have the columns MSN, Date and Day Dif, I want to add the columns calculating the missing dates, in the format below.
MSN | Date | Day Dif | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
116403 | 05/01/2015 | 1 | 3.22% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 01/01/2014 | 1 | 3.22% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 02/01/2014 | 1 | 3.22% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 03/01/2014 | 1 | 3.22% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 04/01/2014 | 1 | 3.22% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 05/01/2014 | 1 | 3.22% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 06/01/2014 | 1 | 3.22% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 08/01/2014 | 2 | 6.44% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 09/01/2014 | 1 | 3.22% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 02/02/2014 | 24 | 70.90% | 7.14% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 03/02/2014 | 1 | 0 | 3.57% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 04/02/2014 | 1 | 0 | 3.57% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 05/02/2014 | 1 | 0 | 3.57% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 06/02/2014 | 1 | 0 | 3.57% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
116404 | 23/12/2014 | 320 | 0 | 78.57% | 100.00% | 100.00% | 100.00% | 100.00% | 100.00% | 100.00% | 100.00% | 100.00% | 100.00% | 70.97% |
116404 | 24/12/2014 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3.22% |
116404 | 25/12/2014 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3.22% |
116404 | 26/12/2014 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3.22% |
116404 | 27/12/2014 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3.22% |
116404 | 05/01/2015 | 9 | 16.10% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12.88% |
116405 | 01/01/2014 | 1 | 3.22% | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Regards
Hi Richard,
sorry, would you mind elaborating a bit on exactly what you want to do? I'm guessing ...
Hi
good point, I have been looking a this so long , I almost expect every one else to understand .
I am trying to calculate how many days are missing between the date and previous(date) in table, and then calculate which months those missing dates occur. This can be expressed as a percentage of the month or number of days in said month and added to the origional table as above. I will then use table later on in the script.
I hope this makes sense
Try this, Peek would fetch previous records and returns zero, if there isn't any.
Load
Date(Floor(Date))-Peek(Date(Floor(Date) as Dates_Diff,
(Date(Floor(Date))-Peek(Date(Floor(Date) /Floor(MonthEnd(Date))-Floor(MonthStart(Date))+1)*100 as [% of Days Missing]
From Table
Order by Date.
Use Peek or Previous Function to get the previous row value.