Last Day in each month in the script

[mario-sarkis]

hey ALL,

I have a question , if i have a table look like this

ID        PeriodDate     Amount

x          1/2/2015         1000

x          1/22/2015       1100

x          1/25/2015       1500

x          2/5/2015         1200

x          2/15/2015       1300

x          2/20/2015       1600

y          1/2/2015         1100

y          1/15/2015       1500

y          1/22/2015       2000

y          2/1/2015         1300

y          2/10/2015       1100

y          2/27/2015       1200

i need to show it like this in the script:

x          1/25/2015       1500

x          2/20/2015       1600

y          1/22/2015       2000

y          2/27/2015       1200

how can i do it in the script?

Thank you in advance,

[tyagishaila]

Dates:

LOAD Day(Period) as MaxDay,

    Month(Period) as Month,

    Period,

    ID,

    Amount

    INLINE [

    ID, Period, Amount

    x, 1/2/2015, 1000

    x, 1/22/2015, 1100

    x, 1/25/2015, 1500

    x,2/5/2015,1200

  x,2/15/2015,1300

  x,2/20/2015,1600

  y,1/2/2015,1100

  y,1/15/2015,1500

  y,1/22/2015,2000

  y, 2/1/2015,1300

  y,2/10/2015,1100

  y,2/27/2015,1200

];

Inner join

TAB2:

Load 

    Max(Day(Period)) as MaxDay,

    Month(Period) as Month,

    ID   

Resident Dates

group by ID,Month(Period)

;

[mario-sarkis]

hi Pardib this my sampl Data i used your Expression it works but my request is i need to show a table that calculate the amount in max date VS  the amount in the Previous date right before it here is my example:

ID	Balance	Date
07019	20994.09	3/19/2015
07019	21035.58	11/26/2014
07019	21077.07	12/31/2013
07019	20007	1/31/2014
07019	17000	2/28/2014
07019	18000	4/30/2014
07019	12000	5/31/2014
07019	10000	6/30/2014
07019	14000	7/31/2014
07019	25000	8/31/2014
07019	55000	9/25/2014
07019	153000	10/17/2014
00534	144551	3/19/2015
00534	153420	11/26/2014
00534	161708	12/31/2013
00534	25000	1/31/2014
00534	26000	2/28/2014
00534	29000	4/30/2014
00534	35000	5/31/2014
00534	45000	6/30/2014
00534	26000	7/31/2014
00534	29000	8/31/2014
00534	22000	9/25/2014
00534	21000	10/17/2014

in this Case i need my output will show if no selection on any field the Amount on max date VS the previous one in my example :

ID                       MaxDate (3/19/2015)            PreviousDate(11/26/2014)       Lost Amount

07019                20994.09                                21035.58                                    20994.09-21035.58=(41.49)

00534                144551                                   153420                                       153420-144551=8869

if i select on for example  10/17/2014 i get

ID                       MaxDate (10/17/2014)            PreviousDate(9/25/2014)       Lost Amount

07019                153000                                      55000                                        153000  - 55000=98000

00534                21000                                        22000                                         21000-22000  =(1000)

Hope you can help thanks

[ankitbisht01]

Here is one more simple script and you can also find attached solution qvw.

A:

load ID,

  PeriodDate,

    month(PeriodDate) as month,

    Amount

    ;

LOAD * INLINE [

    ID  ,      PeriodDate,    Amount

    x  ,      1/2/2015  ,1000

    x  ,      1/22/2015  ,1100

    x  ,      1/25/2015  ,1500

    x  ,      2/5/2015  ,1200

    x  ,      2/15/2015  , 1300

    x  ,      2/20/2015  ,1600

    y  ,      1/2/2015  ,1100

    y  ,      1/15/2015  ,1500

    y  ,      1/22/2015  ,2000

    y  ,      2/1/2015  ,1300

    y  ,      2/10/2015  ,1100

    y  ,      2/27/2015  ,1200

];

Inner Join(A) /* we are doing  inner join and not droping the  table A  and loading all fileds in table B. because table A is                        having 'amount' field which will  not work with Group by function without using sum(amount) in this case                        it will not provide desired result as  it will sum up all the values.

                  */

B:

Load ID,

  month,

  Date(Max(PeriodDate)) as PeriodDate

  Resident A Group By ID, month;

Regards

Ankit Bisht