0 Replies Latest reply: Nov 17, 2016 5:54 PM by Richard Judkins RSS

    Keeping YTD relative to MTD in a table

    Richard Judkins

      Hey all

       

      So I hope this is going to make sense (it does in my head!).

       

      I have a YTD formula:

      Sum({<Year=, Month=, Quarter=, Week=, DateField=, TRANS_DATE={">=$(=Num(YearStart(Max(TRANS_DATE))))<=$(=Max(TRANS_DATE))"}>} QTY)

       

      And MTD:

      Sum({<Year=, Month=, Quarter=, Week=, DateField=, TRANS_DATE={">=$(=Num(MonthStart(Max(TRANS_DATE))))<=$(=Max(TRANS_DATE))"}>} QTY)

       

      And a couple of Filter Panes where I can select the month and year.

       

      Now, this is what is happening. With no month or year selected, the MTD and YTD is fine. It shows the current month's (November) QTY so far, and the YTD.

      However, when I select October (selecting the previous month will be a common occurrence) the month result is still fine (shows October's QTY).

      But - the YTD now mirrors the October QTY.

       

      What I want the YTD to show is the  YTD QTY as of the end of the month selected.

       

      I have little to no clue how to fix it (having only been working with Qlik for 3 weeks). Any help greatly appreciated thanks!