topic Re: QTD, MTD and YTD Issue in QlikView

QTD, MTD and YTD Issue

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

Hello everyone,

I am currently working on sales report and trying to add QTD, MTD and YTD to it. I got the expression below but sadly previous year value is 0. I've been trying to find what the issue is but couldn't. I checked my code with other topics but seems the same so I wanted to get your opinion.

QTD :

Sum({<YIL={'$(vPriorYear)'}, AY=, CEYREK=, FATURATARIHI={'<=$(vPriorYearDate)'}, DateNum={">=$(=Num(QuarterStart(Max(DateNum), -4)))<=$(=AddYear(Max(DateNum), -1))"}>} SATIRNETTUTARI)

MTD:

Sum({<YIL={'$(vPriorYear)'}, AY=, CEYREK=, FATURATARIHI={'<=$(vPriorYearDate)'},GUN={'<=$(vMaxDay)'}, DateNum={">=$(=Num(MonthStart(Max(DateNum), -12)))<=$(=AddYears(Max(DateNum), -1))"}>} SATIRNETTUTARI)

YTD:

Sum({<YIL={'$(vPriorYear)'}, AY=, CEYREK=, FATURATARIHI={'<=$(vPriorYearDate)'}, DateNum={">=$(=Num(YearStart(Max(DateNum), -1)))<=$(=AddYear(Max(DateNum), -1))"}>}SATIRNETTUTARI)

Thanks in advance,

Re: QTD, MTD and YTD Issue

sunny_talwar — Fri, 15 May 2020 13:12:15 GMT

Try this for QTD and see if this works.

Sum({<YIL, AY, CEYREK, FATURATARIHI, DateNum={"$(='>=' & Num(QuarterStart(Today(), -4)) & '<=' & Num(AddYears(Max(Today()), -1)))"}>} SATIRNETTUTARI)

Re: QTD, MTD and YTD Issue

Emir_Peker — Fri, 15 May 2020 13:42:13 GMT

Yes, it did work. I guess all I had to do was remove the vPriorYear. Do the same with others I guess?

Re: QTD, MTD and YTD Issue

sunny_talwar — Fri, 15 May 2020 13:44:56 GMT

Also added a Num() function around AddYears (note the function is AddYears with a s, not AddYear). Finally, removed {'<=$(vPriorYearDate)'}. Basically, DateNum drives your period selection needed

Re: QTD, MTD and YTD Issue

Emir_Peker — Fri, 15 May 2020 13:57:06 GMT

I see, thank you.

If I don't add Num() function still work on MTD and YTD. So do I really need to add it?

Sum({<YIL, AY, CEYREK, FATURATARIHI, GUN={'<=$(vMaxDay)'}, DateNum={">=$(=Num(MonthStart(Max(DateNum), -12)))<=$(=AddYears(Max(DateNum), -1))"}>} SATIRNETTUTARI)

Also after using your suggested code, my present QTD, MTD and YTD shows 0. Codes are below.

QTD:

Sum({<YIL, AY, CEYREK, FATURATARIHI, DateNum={"$(='>=' & Num(QuarterStart(Max(DateNum)) & '<=' & Num(AddYears(Max(DateNum)))"}>} SATIRNETTUTARI)

MTD:

Sum({<YIL, AY, CEYREK, FATURATARIHI, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} SATIRNETTUTARI)

YTD:

Sum({<YIL, AY, CEYREK, FATURATARIHI, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} SATIRNETTUTARI)

Re: QTD, MTD and YTD Issue

Brett_Bleess — Wed, 10 Jun 2020 21:22:12 GMT

@sunny_talwar You have time to take one last look at this one for us? Thanks in advance and sorry for the bother, but I figured you were likely the best one to follow-up given you know what you were thinking, and you know Set Analysis is not my best area! 🙂

Cheers,
Brett