topic Re: LY YTD Acumulative in QlikView

LY YTD Acumulative

Tue, 09 Oct 2012 23:59:53 GMT

Hi Folks,

I'm strugglin with the LY YTD sales calculation. What I need is to get the Sum of sales for the acumulation of the last year until the same period that we are now. So for example. now we are in Fiscal Period 11 (Due to our Fiscal period started on December), what I need is the acumulative sales for FY 2011 which is from Dec 2010 to Oct 2011.

So what I did is something like this:

Sum ({$<[Fiscal Year]= {$(#vPriorFiscalYear)}, [Fiscal Period]= {$("<="$(@FP_CURRENT))}>} CustProd_SaleAmtLocalCurr)

Of course the ${"<=" is not working as I expected.

Any idea would be very apreciated!!

Thanks!

Re: LY YTD Acumulative

Wed, 10 Oct 2012 00:54:32 GMT

Just to add something that I did.

Now I'm testing this one but the result is rare:

=Sum({$<[Fiscal Year]= {$(#vPriorFiscalYear)}, [Fiscal Period]= p({<[Fiscal Year]={$(=Only([Fiscal Year]))}>} [Fiscal Period])>} CustProd_SaleAmtLocalCurr)

Using this [Fiscal Period]= p({<[Fiscal Year]={$(=Only([Fiscal Year]))}>} [Fiscal Period]) I'm expecting to get only the valid fiscal period for 2012 (which are 1 to 10), but instead the sum is returning the same value as the complete fiscal year 2011. It seems the p is not working as I expected.

Re: LY YTD Acumulative

Wed, 10 Oct 2012 03:25:38 GMT

Hi,

Do you have a sample data or qvw file?

Regards,

Janzen

Re: LY YTD Acumulative

Wed, 10 Oct 2012 14:41:35 GMT

Well, the solution was there all the time. The P is working perfect. I was the one that was not working so well!!

The issue was in the selection. I had selected fiscal period so that why I got the same value for both fiscal year. Anyway, I have add a little extra in the formula just to consider the closed fiscal period. So now the completed and working formula is like this:

Sum({$<[Fiscal Year]= {$(vPriorFiscalYear)},

[Fiscal Period]= p({$<[Fiscal Year]={$(=Only([Fiscal Year]))}>} [Fiscal Period])-{$(=max([Fiscal Period]))}>} CustProd_SaleAmtLocalCurr)