topic YTD Expression in QlikView

YTD Expression

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me on below requirement.

I have a date field called [Report Date] in DD/MM/YYYY format.

I have to calculate the YTD in the below set analysis

i.e, The number of issues that have been completed from jan 01 to till the previous month.

I have many years in filter pane i have to restrict to max(year) jan 01 to till the previous month

My set analysis expression is

=count({<Status={'Completed'}>}distinct Issues)

Please help me to write the above YTD expression.

Thanks in advance.

Re: YTD Expression

sudhirpkuwar — Thu, 24 May 2018 10:29:44 GMT

try this

=count({<Status={'Completed'},[Report Date]={">=YearStart(Max([Report Date])) <MonthStart(Max([Report Date]))"}>}distinct Issues)

this will calculate from 1 st of Jan to Previous month end

Re: YTD Expression

shiveshsingh — Thu, 24 May 2018 10:30:20 GMT

Can you try this?

Count({<Status={'Completed'},[Report Date]=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum)-1)"}>} distinct Issues)

where DateNum = Floor([Report Date])

Re: YTD Expression

Anil_Babu_Samineni — Thu, 24 May 2018 10:31:37 GMT

May be this?

=count({<Status={'Completed'}, DateField = {">=$(=YearStart(Max(DateField)))<=$(=AddMonths(Max(DateField),-1))"}, Month, Day, Week, Quarter>}distinct Issues)

Re: YTD Expression

sunny_talwar — Thu, 24 May 2018 11:21:50 GMT

Or this

=Count({<Status = {'Completed'}, [Report Date] = {">=$(=Date(YearStart(Max([Report Date])), 'DD/MM/YYYY'))<$(=Date(MonthStart(Max([Report Date])), 'DD/MM/YYYY'))"}, Month, Day, Week, Quarter>} DISTINCT Issues)