last day from dimension month

[#1]

Hi everybody,

I want show a count of all tickets whose day is equal last day of month.

I have used this answer of Vishwaranjan Kumar, but i can't display a sum correct.

can you help me?

Thanks.

[datanibbler]

Hi Jose,

if you have only a month, that's rather easy, use MONTHEND() to get the last day of the month - an exception would be February, but in that case you can't do without a year.

If you have a year, you could use two nested functions: MONTHEND(MAKEDATE([Year_field], [Month_field], [Day_field]])) - you could just use a 1 in case you don't have a [day_field]

HTH

Best regards,

DataNibbler

[#3]

Hi José,

You could use the Monthend and Dayend functions in your expression:

     =sum(if(MonthEnd(Day)=DayEnd(Day),NumTicket,0)):

Attention: you'd better remove the apostrophes at the end of the dates in your inline load, to make it easier to really use them as dates. On top of that I had to format them into dates by using date#(Day,'MM-DD-YYYY').

regards,

Jo

[MarcoWedel]

Hi José,

one solution could be:

maybe it's also possible to extract the month from your day field?:

LOAD *,
    Month(Day) as Month
INLINE [
    Day, Process Type, NumTicket, Year
    1-31-2014, New, 1, 2014
    1-22-2014, New, 2, 2014
    1-20-2014, New, 3, 2014
    1-31-2014, New, 4, 2014
    1-31-2014, New, 5, 2014
    2-28-2014, New, 6, 2014
    2-26-2014, New, 7, 2014
    2-28-2014, New, 8, 2014
    2-28-2014, New, 9, 2014
    2-26-2014, New, 10, 2014
    2-28-2014, New, 11, 2014
    2-28-2014, New, 12, 2014
];

hope this helps

reagrds

Marco

[#5]

Thanks all,

Marco, your answer it's right, but i need take max(day) of month, and i can't do this. If i modify your formula, tried this:

=- sum([Process type] = 'New' and Day=Max(Day))

this not right.

how i do it??

[#6]

Anybody here??

I try to use a Max(Day) in set analysis. Can anybody help me??

I created a new table with the last day of each month, but when I used in set analysis, don't compared a day with last day.

I need help. I have tried all possibilites but anything is right.

Best regards

[er_mohit]

See the attached file

hope it helps

[#8]

Hi Mohit

thanks, but this method is only valid when you need show a single month. I would like to show last day of all months data.

I have tried use a variable for calculate a last day of month, but i had same result than you.