4 Replies Latest reply: Nov 15, 2016 10:33 AM by jeevayswaran a

# get sum of values of first date of each month

Hi frds,

Date               MonthYr           Amonut

1-Jan-2016        Jan-2016          10

2-Jan-2016       Jan-2016            20

5-Feb-2016       Feb-2016          30

3-Feb-2016       Feb-2016           40

3-Mar-2016       Mar-2016          50

Above is my data.

my expected answer is:

output:

MonthYr     Amount

Jan-2016     10

Feb-2016     40

Mar-2016     50

The output is based on first date value of each month, if i select a date the month should appear minimum date month to maximum date month. for example if i select 5-Feb-2016 in date field the result is,

MonthYr     Amount

Jan-2016     10

Feb-2016     40

please suggest me.

• ###### Re: get sum of values of first date of each month

Hi, if there is only one amount per date you can use FirstSortedValue:

FirstSortedValue({<Date={"<=\$(=Max(Date))"}>} Amount, Date)

• ###### Re: get sum of values of first date of each month
```TempData:
Load
Date(Date#(Date,'D-MMM-YYYY')) as Date,
Date#(MonthYr,'MMM-YYYY') as MonthYr,
Amount
Inline
[
Date, MonthYr, Amount
1-Jan-2016, Jan-2016,   10
2-Jan-2016, Jan-2016,   20
5-Feb-2016, Feb-2016,   30
3-Feb-2016, Feb-2016,   40
3-Mar-2016, Mar-2016,   50
];

Left Join (TempData)
Load MonthYr, Min(Date) as MinDate Resident TempData Group By MonthYr;

FinalData:
Load *, If(Date = MinDate,1) as Flag Resident TempData;

Drop Table TempData;
Drop Field MinDate;
```

Create a Straight Table

Dimension

MonthYr

Expression

SUM({<Flag = {1}, Date = {'<=\$(=Max(Date))'}>}Amount)

• ###### Re: get sum of values of first date of each month

And if you don't want to change the script, use as below.

Straight Table

Dimension

MonthYr

Expression

SUM({<Date = {'<=\$(=Max(Date))'}>}IF(Date = Aggr(NODISTINCT Min({1}Date),MonthYr), Amount))

• ###### Re: get sum of values of first date of each month

Hi manish,

Excellent !!!

Thank you so much.