7 Replies Latest reply: Mar 18, 2016 1:16 AM by Swetha More RSS

    YTD with a custom fiscal year

    Swetha More

      Dear Experts,

       

      I am working on a report which shows the monthly sales comparison of the current month of the current year vs the current month of the previous year (Example comparing – Feb 2015 and Feb 2016 in pivot tables) . The month selection is dynamic which lets the users choose which monthyear they want to compare. Along with this we also calculate the Year to Date(Example for Feb 2015 the year to date calculation should be from 01/10/2015 to 29/02/2016, since our fiscal year is from Oct to Oct).


      The monthy comparison works just fine. However I am eternally stuck at YTD  calculation.

      I have the below expression to calculate YTD:


      Sum({$<[number]={'*SINV*'},[res_partner.name]={*}-{''},[date_invoice]={'>=$(YearStart(MonthStart($(MakeFinDate)),0,10))<=$(MonthEnd($(CurrMonth)))'}>}amount_untaxed)


      Variable Definitions:

      FinDate=’01/10/2000’

      CurrMonth=Max([date_invoice])

      MakeFinDate=SetDateYear($(FinDate),Year($(CurrMonth))-1)


      If the user chooses the months as Feb 2015 and Feb 2016 for comparison then the Feb 2016 YTD should give me a cumulative amount starting from Oct 2015 to Feb 2016 but this expression does not give me that value, instead it just simply adds up the amount of Feb2015

      and Feb 2016.


      Can someone please verify the expression and throw some light as to where the issue must be. Many Thanks.



      Note: I am a beginner in Qliksense hence my expression is not highly optimized and is very naive.


      Thanks

      Swetha