topic Fiscal LYTD problem in QlikView

Fiscal LYTD problem

sifatnabil — Sat, 12 Apr 2014 16:38:21 GMT

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.

Re: Fiscal LYTD problem

Sat, 12 Apr 2014 19:48:57 GMT

Hi,

Can you attach an example file if possible?

Re: Fiscal LYTD problem

Sun, 13 Apr 2014 22:43:34 GMT

please try:

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

Re: Fiscal LYTD problem

shawn-qv — Sun, 13 Apr 2014 23:25:41 GMT

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:

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.
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,

Re: Fiscal LYTD problem

sifatnabil — Mon, 14 Apr 2014 00:32:50 GMT

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!