Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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.
Hi,
Do you have a sample data or qvw file?
Regards,
Janzen
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)