Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two field i.e. - Invoice Date and Amount, I want 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 | 30/04/2015 | 1191 | |
05/01/2015 | 41 | 01/05/2015 | 1201 | |
06/01/2015 | 51 | 02/05/2015 | 1211 | |
07/01/2015 | 61 | 03/05/2015 | 1221 | |
08/01/2015 | 71 | 04/05/2015 | 1231 | |
09/01/2015 | 81 | 05/05/2015 | 1241 | |
10/01/2015 | 91 | 06/05/2015 | 1251 | |
11/01/2015 | 101 | 07/05/2015 | 1261 | |
12/01/2015 | 111 | 08/05/2015 | 1271 | |
13/01/2015 | 121 | 09/05/2015 | 1281 | |
14/01/2015 | 131 | 10/05/2015 | 1291 | |
15/01/2015 | 141 | 11/05/2015 | 1301 | |
16/01/2015 | 151 | 12/05/2015 | 1311 | |
17/01/2015 | 161 | 13/05/2015 | 1321 | |
18/01/2015 | 171 | 14/05/2015 | 1331 | |
19/01/2015 | 181 | 15/05/2015 | 1341 | |
20/01/2015 | 191 | |||
21/01/2015 | 201 | |||
22/01/2015 | 211 | |||
23/01/2015 | 221 | |||
24/01/2015 | 231 | |||
25/01/2015 | 241 | |||
26/01/2015 | 251 | |||
27/01/2015 | 261 | |||
28/01/2015 | 271 | |||
29/01/2015 | 281 | |||
30/01/2015 | 291 | |||
31/01/2015 | 301 | |||
01/02/2015 | 311 | |||
02/02/2015 | 321 | |||
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 | |||
02/05/2015 | 1211 | |||
03/05/2015 | 1221 | |||
04/05/2015 | 1231 | |||
05/05/2015 | 1241 | |||
06/05/2015 | 1251 | |||
07/05/2015 | 1261 | |||
08/05/2015 | 1271 | |||
09/05/2015 | 1281 | |||
10/05/2015 | 1291 | |||
11/05/2015 | 1301 | |||
12/05/2015 | 1311 | |||
13/05/2015 | 1321 | |||
14/05/2015 | 1331 | |||
15/05/2015 | 1341 |
Load [Invoice Date], Amount
From ...source....
Where floor([Invoice Date]) = floor(monthend([Invoice Date]));
PFA,but u can try for some better expression as well.
This might be exactly want you are looking for - Need a calculated dimension with an If() :
Hi Petter,
I need the same in script.
Thanks for Reply.
Hi Gysbert,
It will give every monthend date. But requirement is like this.
I need the same in script.
1. Suppose I have 6 month data from 1st Jan 2015 to 1 July 2015 with amount. Then I want from Jan to May 2015, the day will come for end of the month date. Like 31st Jan, 28th Feb, 31 Mar, 30th Apr and 31st May. And for June month entire date will come, for 1st July 2015 will not come.
2. But if 2nd July is available then 31st Jan, 28th Feb, 31 Mar, 30th Apr, 31st May, 30th June, 1st July and 2nd July will be there. Similarly, it will continue and till 30 it will continue again for next month when 1st will come then again point 1 will apply.
Thanks for reply and sorry I did not check that, so i have applied 2 times. It was not my intention.
can do a group by on date field.
Load max(datefield), amount resident table group by datefield
Max function will return max date of every month.
Giving you a example :
For fiscal year Jan to current month - May is going. today is 15 - May 2015.
I want from Jan to April it should to return monthend date and for current month may 1st to 14th May date will also return.
Ok, you're forgiven
But please have a bit more patience in the future.
See attached qvw for a script based solution.
Hi Gysbert,
Thanks for reply.
Please find the attached qvw. In the same scenerio if 1st June is coming it is stoping and returning me for Jan to May monthend date.
But output will be - From Jan to Apr - Monthend date of jan / feb / march / apr and for may month from 1st to 31st date will come and for June no date will come. But suppose 2nd june or so on date is coming,
From Jan to May - monthend date but for June till date data will be available.
Thanks Again.