QTD, MTD and YTD Issue

[Emir_Peker]

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,

[sunny_talwar]

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)

[Emir_Peker]

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

[sunny_talwar]

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

[Emir_Peker]

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)

[Brett_Bleess]

@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