topic Re: YTD in QlikView

YTD

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

For e.g. if Year selected as 2017 then the data shown will be : Jan 2017 to Sept 2017, same way if year selected as 2015 then the data shown will be : Jan 2015 to Sept 2015.(Dynamically)

Re: YTD

vardhancse — Thu, 05 Oct 2017 06:13:21 GMT

Hi Try,

Note: The format defined in SET DateFormat and your date field format in Qlikview Table should be same, then only this expressions works.

Last 5 Years Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Max(Date), -4))<=$(=Date(Max(Date)))’}>} Sales )

Last 6 Quarters Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Max(Date), -5))<=$(=Date(Max(Date)))’}>} Sales )

Last 12 Months Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Max(Date), -11))<=$(=Date(Max(Date)))’}>} Sales )

Last 15 Weeks Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Max(Date), -14))<=$(=Date(Max(Date)))’}>} Sales )

Last 10 Days Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=Date(Max(Date)-9))<=$(=Date(Max(Date)))’}>} Sales )

Note:

YTD - Year to Date

QTD - Quarter to Date

MTD - Month to Date

WTD - Week to Date

Last YTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Max(Date), -1))<=$(=AddYears(Max(Date), -1))’}>} Sales)

Last QTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Max(Date), -1))<=$(=AddMonths(Max(Date), -3))’}>} Sales)

Last MTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Max(Date), -1))<=$(=AddMonths(Max(Date), -1))’}>} Sales)

Last WTD for the period selected

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Max(Date), -1))<=$(=Date(Max(Date) -7))’}>} Sales)

Re: YTD

balar025 — Thu, 05 Oct 2017 06:26:47 GMT

You can go with Calendar (As Of month).

Please find attached qvw for same.

Re: YTD

its_anandrjs — Thu, 05 Oct 2017 06:27:29 GMT

Try this in your expression

=Sum( {<Year = { ">=$(=Max(Year)) <=$(=Max(Year)-1)" } >} Val )

Re: YTD

devarasu07 — Thu, 05 Oct 2017 06:28:49 GMT

Hi,

Try like this.

YTD Sales

sum({$<Year={"$(=max(Year))"},Month={"<=$(=max(Month))"}>} Sales)

Re: YTD

its_anandrjs — Thu, 05 Oct 2017 06:35:22 GMT

Earlier a typo this is right

=Sum( {<Year = { "<=$(=Max(Year)) >=$(=Max(Year)-1)" } >} Val )

Re: YTD

Kushal_Chawda — Thu, 05 Oct 2017 06:55:02 GMT

sum({<Year={"$(=max(Year))"},Year=,Month=>}Sales)

Re: YTD

soniasweety — Thu, 05 Oct 2017 10:42:48 GMT

if I want to MTD,QTD

how can I write the expression in this?