Fiscal LYTD problem

[sifatnabil]

Hi,

I can get the YTD for a fiscal year starting April, but not the LYTD. The YTD works successfully with this set analysis:

=sum({<fYear={$(vMaxYear)},Date=,fMonth=>} Revenue)   Where vMaxYear=Max(FYear).

I attempted the LYTD with vLastMaxYear=vMaxYear-1, and put this in the fYear= part of the above set analysis, but it showed me the entire last year's total. How can I modify this to get LYTD? Thanks.

[#2]

Hi,

Can you attach an example file if possible?

[#3]

please try:

sum({<fYear={$(=Max(FYear)-1)},Date=,fMonth=>} Revenue) 

[shawn-qv · Accepted solution]

Hi Sifat,

For LYTD, I don't think you should ignore the Date in your set analysis, otherwise, you'll get the last year's entire revenue. E.g. By specifying Date=, fMonth= , you're basically ignoring the Date and Month, which leaves you with just fYear = vLastMaxYear.

Here is what you can try:

1. Remove Date and fMonth from your set analysis.
   NOTE: This method may not make sense if you're wanting to explicitly show the LYTD regardless of the Day or Month the user has selected. To get a true YTD, try the method below.
   
2. Ignore fMonth and fYear (and fDay too if you have it as a filter), and construct a date range to select on the Date field. E.g. <fMonth=, fYear=, Date = {">=1/04/2013<=14/04/2014"}.
   
   Of course you can store the Start and End date in a variable (for re-using), or construct the date on the fly:
   a. < fMonth=, fYear=, Date = {">= $(vLastYearStartDate) <= $(vCurrentDate)"} >
   b. < fMonth=, fYear=, Date = {">= $(=makedate(vLastMaxYear, month(today(2), day(today(2)))"  <= $(=max(Date)) >"

Hopefully that'll give you some ideas.

Cheers,

S

[sifatnabil]

Hi all,

Thanks for your responses. I figured out the problem before these replies were made. It's essentially what Shawn-QV mentioned - my original set analysis was selecting the entire current year, and so attempting LYTD was selecting the entire last year too.

The fix is to set a current date (I've also set a current month), and do a "less than", which will sum the correct number of days for last year:

sum({<fYear={$(vLastMaxYear)},Date=,DayNumber={'<=$(vCurrentDay)'},Month={$(vCurrentMonth)}, FMonth=>} $(Revenue))

Thanks for all your help!