Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

LY YTD Acumulative

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!

1 Solution

Accepted Solutions
Not applicable
Author

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)

View solution in original post

3 Replies
Not applicable
Author

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.

Not applicable
Author

Hi,

Do you have a sample data or qvw file?

Regards,

Janzen

Not applicable
Author

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)