Last day of month

[David_K]

Good Morning fellow Qlik Sense users,

Wishing you al happy and prosperous new year.

I have a table detailing the number of inspections carried out every day.  I need to be able to create a table that shows the number carried out on the last day of the month.

I have tried 

max(Date(date(Date,'DD/MM/YYYY')))

=Aggr(Max(Date), Month)

The above scripts show as ok in the expression editor, but when confirmed the they show as an invalid expression.

The date filed I am using is called Inspection_Date,  

Any support or guidance would be most gratefully received.

David 

[Parthiban]

Hi,

If you want month end date means use monthend function in your script.

ex: =MonthEnd(Today()). This will return monthend date. Then you count the number of carried out that particular date.

[kfoudhaily]

Hi,

create a field in script in the same table as inspection date

load ... ,
if(date(inspection_Date=date(monthend(Inspection_Date)),'True') as flah_monthend
from ..

then in front create set analysis 

Count({$<flah_monthend={'TRUE'}>} DISTINCT inspections)

regards,

[Aditya_Chitale]

If you are including Date column in your table chart, you can simply write an if() condition to show only last month ending date entries and untick include null values to remove remaining date entries. Something like this:

=if(Inspection_Date = Monthend(Addmonths(today(),-1)), Inspection_Date, null())

You can replace today() in above expression with max(Inspection_Date) as per your requirement and make sure to adjust date format.

Also you were getting error in you posted expression because maybe you were adding it as a dimension instead you should add it in table as measure.

Regards,

Adiitya