Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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!

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: LY YTD Acumulative

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)

3 Replies
Not applicable

Re: LY YTD Acumulative

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

Re: LY YTD Acumulative

Hi,

Do you have a sample data or qvw file?

Regards,

Janzen

Not applicable

Re: LY YTD Acumulative

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)

Community Browser