topic Re: last day from dimension month in QlikView

last day from dimension month

Fri, 13 Jun 2014 13:42:46 GMT

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.

Re: last day from dimension month

datanibbler — Fri, 13 Jun 2014 13:49:00 GMT

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

Re: last day from dimension month

Fri, 13 Jun 2014 14:21:16 GMT

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,

Re: last day from dimension month

MarcoWedel — Fri, 13 Jun 2014 16:00:19 GMT

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

Re: last day from dimension month

Mon, 16 Jun 2014 12:51:56 GMT

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??

Re: last day from dimension month

Wed, 18 Jun 2014 10:26:11 GMT

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

Re: last day from dimension month

er_mohit — Wed, 18 Jun 2014 11:21:15 GMT

See the attached file

hope it helps

Re: last day from dimension month

Wed, 18 Jun 2014 11:57:28 GMT

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.