6 Replies Latest reply: Feb 29, 2012 4:56 AM by Quentin Raith

To find the sum of a field for last day of month

Hi,

I need to find the sum of a field for last day of month

I have the data in  foll format

Date                                Pdt            SalesCount

2012-01-01   A                         100

2012-01-01   B                         100

2012-01-02   A       100

2012-01-02   B                         200

..

..

2012-01-31   A      300

2012-01-31   B      400

2012-02-01   A      200

2012-02-01   B      300

2012-02-02   A      400

2012-02-02   B      500

For each month, i need the sum of the SalesCount for last day of the month. In the above example, for Jan, i need the sum of sales count for 31st Jan and for Feb, i need the sum of salescount for 2nd Feb

I have Month as a dimension

Expected output:

2012 Jan  = 700

2012 Feb  = 900

• To find the sum of a field for last day of month

Hi,

Date,

If(Date = MonthEnd(Date), 1, 0) AS IsMonthEnd,

'

'

'

FROM DataSource;

Now by the month end has 1 in IsMonthEnd Field.

In expression now you get the values as

=Sum({<IsMonthEnd={1}>} SalesCount)

Hope this helps you.

Regards,

Jagan.

• To find the sum of a field for last day of month

Hi.

MonthEnd(Date) gives me last day of the month say for Feb 2012 it gives 29-Feb-2012, but say i have data only till 10th of Feb then i need the sum of salescount for 10th of Feb.

How to achieve this?

• To find the sum of a field for last day of month

Using month as a dimension try this:

sum(if(Date=max(Date),SalesCount))

• Re: To find the sum of a field for last day of month

=sum(aggr(if(Date=max(total<Pdt,Month> Date),SalesCount),Pdt,Month,Date))

• To find the sum of a field for last day of month

Hi,

The expression suggested by Swuehl works perfectly for you scenario.  I think the Date should be removed at the end of the expression.  The modified expression would be

=sum(aggr(if(Date=max(total<Pdt,Month> Date),SalesCount),Pdt,Month))

Regards,

Jagan.

• To find the sum of a field for last day of month

Thank you guys, it helped me a lot to calculate stock level, instead of using firstsortedvalue().

I have one more question: is it possible to integrate set analysis using this expression?

I am using a Master Calendar and would like for i.e. to sum the last 12 months to make average

Thanks,

Quentin