Get only the last day's data for each month

[#1]

Hi all.,

Please help

I need to display the sum of only last day's data for each month, with the current scenario it is displaying a cumulative data all the days data for each , could you please advice me on how to go about it?

Appreciate your help

[Anonymous]

There is a funtion called Monthend

Try to load the Monthend dates from your date field and the you can fill the numbers

[#3]

Hi,

easiest way to do that really is in your script, use the monthend() function to create a new month end flag field

if(Date_field=Monthend(Date_field),1) As MonthEnd_Flag

then in your expression use that within your set analysis

Sum({<MonthEnd_Flag={'1'}>} Sales)

Something like that

hope that helps

Joe

[#4]

You can use MonthEnd() Function

[#5]

Currenctly I am using the following in the calculated dimension to get past 12 months data, I even tried monthend in this but it did not work, if(monthstart(Valuation)= monthstart(addmonths(today(),0)) or monthstart(Valuation)= monthstart(addmonths(today(),-1)) or monthstart(Valuation)= monthstart(addmonths(today(),-2)) or monthstart(Valuation)= monthstart(addmonths(today(),-3)) or monthstart(Valuation)= monthstart(addmonths(today(),-4)) or monthstart(Valuation)= monthstart(addmonths(today(),-5)) or monthstart(Valuation)= monthstart(addmonths(today(),-6))or monthstart(Valuation)= monthstart(addmonths(today(),-7)) or monthstart(Valuation)= monthstart(addmonths(today(),-8)) or monthstart(Valuation)= monthstart(addmonths(today(),-9)) or monthstart(Valuation)= monthstart(addmonths(today(),-10)) or monthstart(Valuation)= monthstart(addmonths(today(),-11)) or monthstart(Valuation)= monthstart(addmonths(today(),-12)),date(monthstart(Valuation),'DD-MMM-YY')) as date

Any suggestions please?

Thanks

[#6]

Hi

Use the below expression:

= Sum({<Date_field = {"=MonthEnd(Date_Field)"}>}Data)

Thanks

Sabal

[Anonymous]

Try

Date(Monthend(Valuation),'DD-MM-YYYY') as MEDate

and then you can try to load the 12 months.

Are you trying to load a rolling 12 months?

[#8]

Thanks for the reply it only displays one date which last day of the month as I used monthend in the if condition while rolling for past 12 months data, but it is still summing data for all the days rather than just displaying last days data,

Please advice on how to go about it

Greatly appreciate your help

[#9]

Hi

Can you please attach your qvw?

Thanks

Sabal

[#10]

HI all ,

I have a same problem.

I'm reading a DB wich contains lot of data , so i split the db in QVD by month

201412 201411 and so on.

I have a problem with the last day of each qvd , data arrive only al the last day -1

E.g for november i have data until 29/11/2014 23:59:59 PM

Here is the script :

for Each a in -1
LET v_start_read_period = MonthStart(today(0)-1,$(a));
LET v_start_read_period_d = Date(MonthStart(today(0)-1,$(a)));
LET v_end_read_period = MonthEnd(today(0)-1,$(a));
LET v_end_read_period_d = Date(MonthEnd(today(0)-1,$(a)));
LET v_period_name = Year(MonthStart(today(0)-1,$(a)))&Mid(Date(MonthStart(today(0)-1,$(a))),4,2);

GPSPositions:

SQL SELECT
,[geoPosition].STX as longitudine
,[geoPosition].STY as latitudine
,[geoPosition].ToString() as geoposition
,[lastUpdateUTC]
,[lastUpdateLT]
FROM "RouteMonitoring_ParkArchive".dbo.ShipGPSPositions
where
type='Actual'
and [lastUpdateUTC] >= convert(datetime,'$(v_start_read_period_d)',103)
and [lastUpdateUTC] <= convert(datetime,'$(v_end_read_period_d)',103)

Thanks for the help