Need Script Help

[#1]

Hi,

I have two field i.e. - Invoice Date and Amount, I need the same out like this :

Invoice Date	Amount	Output -->	Invoice Date	Amount
01/01/2015	1		31/01/2015	301
02/01/2015	11		28/02/2015	581
03/01/2015	21		31/03/2015	891
04/01/2015	31		01/04/2015	901
05/01/2015	41		02/04/2015	911
06/01/2015	51		03/04/2015	921
07/01/2015	61		04/04/2015	931
08/01/2015	71		05/04/2015	941
09/01/2015	81		06/04/2015	951
10/01/2015	91		07/04/2015	961
11/01/2015	101		08/04/2015	971
12/01/2015	111		09/04/2015	981
13/01/2015	121		10/04/2015	991
14/01/2015	131		11/04/2015	1001
15/01/2015	141		12/04/2015	1011
16/01/2015	151		13/04/2015	1021
17/01/2015	161		14/04/2015	1031
18/01/2015	171		15/04/2015	1041
19/01/2015	181		16/04/2015	1051
20/01/2015	191		17/04/2015	1061
21/01/2015	201		18/04/2015	1071
22/01/2015	211		19/04/2015	1081
23/01/2015	221		20/04/2015	1091
24/01/2015	231		21/04/2015	1101
25/01/2015	241		22/04/2015	1111
26/01/2015	251		23/04/2015	1121
27/01/2015	261		24/04/2015	1131
28/01/2015	271		25/04/2015	1141
29/01/2015	281		26/04/2015	1151
30/01/2015	291		27/04/2015	1161
31/01/2015	301		28/04/2015	1171
01/02/2015	311		29/04/2015	1181
02/02/2015	321		30/04/2015	1191
03/02/2015	331
04/02/2015	341
05/02/2015	351
06/02/2015	361
07/02/2015	371
08/02/2015	381
09/02/2015	391
10/02/2015	401
11/02/2015	411
12/02/2015	421
13/02/2015	431
14/02/2015	441
15/02/2015	451
16/02/2015	461
17/02/2015	471
18/02/2015	481
19/02/2015	491
20/02/2015	501
21/02/2015	511
22/02/2015	521
23/02/2015	531
24/02/2015	541
25/02/2015	551
26/02/2015	561
27/02/2015	571
28/02/2015	581
01/03/2015	591
02/03/2015	601
03/03/2015	611
04/03/2015	621
05/03/2015	631
06/03/2015	641
07/03/2015	651
08/03/2015	661
09/03/2015	671
10/03/2015	681
11/03/2015	691
12/03/2015	701
13/03/2015	711
14/03/2015	721
15/03/2015	731
16/03/2015	741
17/03/2015	751
18/03/2015	761
19/03/2015	771
20/03/2015	781
21/03/2015	791
22/03/2015	801
23/03/2015	811
24/03/2015	821
25/03/2015	831
26/03/2015	841
27/03/2015	851
28/03/2015	861
29/03/2015	871
30/03/2015	881
31/03/2015	891
01/04/2015	901
02/04/2015	911
03/04/2015	921
04/04/2015	931
05/04/2015	941
06/04/2015	951
07/04/2015	961
08/04/2015	971
09/04/2015	981
10/04/2015	991
11/04/2015	1001
12/04/2015	1011
13/04/2015	1021
14/04/2015	1031
15/04/2015	1041
16/04/2015	1051
17/04/2015	1061
18/04/2015	1071
19/04/2015	1081
20/04/2015	1091
21/04/2015	1101
22/04/2015	1111
23/04/2015	1121
24/04/2015	1131
25/04/2015	1141
26/04/2015	1151
27/04/2015	1161
28/04/2015	1171
29/04/2015	1181
30/04/2015	1191
01/05/2015	1201

Please suggest what I should to do in my script for the same output.

Thanks for help in advance.

[maxgro]

LOAD

     [Invoice Date],

     Amount

FROM

TEST.xlsx

(ooxml, embedded labels, table is Data)

Where

  ([Invoice Date] = floor(MonthEnd([Invoice Date])) and [Invoice Date]<makedate(2015,4))

  or

  ([Invoice Date]>= monthstart(makedate(2015,4)) and [Invoice Date]<=MonthEnd(MakeDate(2015,4)))

;

[ToniKautto]

Add a calendar table with a flag showing the last date of the month.

Use a set expression that targets the last date of the month.

Calendar with period flags

Please remember to mark answers as helpful or correct, so that other community members know if further input is required.

[ToniKautto · Accepted solution]

I think I read your question a bit too quickly. Here is a updated version where all the last dates of each month is flagged, and all dates in the last full month. I hope you get the principle on how to approach the issue from my examples.

[ToniKautto]

This is a duplicate of script help

I have closed this thread.