Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

General Date Scripting help

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.

MSNDateDay DifJanFebMarAprMayJunJulAugSepOctNovDec
11640305/01/201513.22%00000000000
11640401/01/201413.22%00000000000
11640402/01/201413.22%00000000000
11640403/01/201413.22%00000000000
11640404/01/201413.22%00000000000
11640405/01/201413.22%00000000000
11640406/01/201413.22%00000000000
11640408/01/201426.44%00000000000
11640409/01/201413.22%00000000000
11640402/02/20142470.90%

7.14%

0000000000
11640403/02/201410

3.57%

0000000000
11640404/02/2014103.57%0000000000
11640405/02/2014103.57%0000000000
11640406/02/2014103.57%0000000000
11640423/12/2014320078.57%100.00%100.00%100.00%100.00%100.00%100.00%100.00%100.00%100.00%70.97%
11640424/12/20141000000000003.22%
11640425/12/20141000000000003.22%
11640426/12/20141000000000003.22%
11640427/12/20141000000000003.22%
11640405/01/2015916.10%000000000012.88%
11640501/01/201413.22%00000000000

Regards

4 Replies
datanibbler
Esteemed Contributor

Re: General Date Scripting help

Hi Richard,

sorry, would you mind elaborating a bit on exactly what you want to do? I'm guessing ...

Not applicable

Re: General Date Scripting help

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

Not applicable

Re: General Date Scripting help

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.

Not applicable

Re: General Date Scripting help

Use Peek or Previous Function to get the previous row value.

Community Browser