10 Replies Latest reply: Mar 27, 2017 9:19 AM by omar bensalem RSS

    YTD, MTD issue

    Nicholas Truss

      Hi,

       

      Having a bit of trouble creating a YTD/MTD measure in qlik sense.

       

      I have read the below and it's not making a lot of sense to me unfortunately.

       

      YTQ, QTD, MTD and WTD

       

       

      My Date Field is called DATE in the table. So I have entered Floor(DATE) as DateNum in my script

       

      I've also created

      LET vToDate = Today();

      LET vToDatePrevious = Today()-365;

       

      However when I add the last formula for the above discussion to my measure it does not filter YTD but shows the total overall for all years. See formula below, may be long winded but I'm very new to this.

       

      Sum(    {<DELETED_FLAG={'0'}, TYPE = {'SI' , 'SC'} , NOMINAL_CODE = {'4000', '4101' , '4100' , '4002' , '4003' , '4102'} ,Year=, Month=, Quarter=, Week=, DATE=, DateNum={">=$(=Num($(=vToDate& 'Start(Max(DateNum))')))<=$(=Max(DateNum))"}  >}    NET_AMOUNT)

       

      Can anyone point out my error please?

       

      Thanks,

       

      Nick

        • Re: YTD, MTD issue
          omar bensalem

          Hi Nicholas,

           

          Don't panic, set expressions seems difficult at first sight, but then you'll love working with them.

           

          Let's suppose you have a date field (you don't have to create variables); only a date field will do the job.

           

          And let's suppose you've created a calendar in your script so you have Year,Month, Quarter and Week fields

           

          YTD: if we select 12/04/2016: YTD will return our measure from 01/01/2016 to 12/04/2016

          How we do that?

           

          Suppose our measure is : sum(Sales)

           

          1)First changes: sum({<date=,Year=,Month=,Quarter=>}Sales)

           

          We add these to force Qlik to not take into consideration our selection of date for example.

          Let me explain in better words, if you don't write the "date=" and select the date 12/04/2016; Qlik will filter all the data to that selected point and then return the sum(Sales) for the day 12/04/2016.

          To prohibit this, we must write the date=.

           

          2) Second change: sum({<date=,Year=,Month=,Quarter=, date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}>}Sales)

           

          Let explain this : date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}

           

          We want to work from date=01/01/2016 to the selected date=12/04/2016 right?

          So we're working with the field :

          a) date={    }

          b) Now we wanna this date to be <=selected date which is max(date) ;

          max(date) is a function so it needs an "=" sign:

          =max(date)

          when we have a '=' we add the $ (before each calculation) : $(=max(date) ) => this is 12/04/2016

          Now we add the <= so we'll have :  <=$(=max(date) )


          for the second part, we want our date to be >=01/01/2016 which is the start of the year:

          a) same approach, we use the YearStart function that returns the start of the selected year: >=$(=YearStart(Max(date)))


          Now our expression is : from : date={    }

          to : date={>=$(=YearStart(Max(date)))<=$(=Max(date))}



          Now, in a set expression, if we wanna work with the year 2016 for example which is numeric: we call it without quotes:

          Year={2016}

          If we wanna focus on a country, Tunisia for example which is a string: we call it with quotes

          Country={'Tunisia'}

           

          In our case, we focusing on a range of dates created by an expression, so we surround it by double quotes:

           

          date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}


          Final expression for YTD:


          sum({<date=,Year=,Month=,Quarter=, date={">=$(=YearStart(Max(date)))<=$(=Max(date))"}>}Sales)


          Same approach for MTD:

          sum({<date=,Year=,Month=,Quarter=, date={">=$(=MonthStart(Max(date)))<=$(=Max(date))"}>}Sales)


          Hope this helps,

          Omar,

            • Re: YTD, MTD issue
              Nicholas Truss

              Hi Omar,

               

              Thanks for the above, however I still can't seem to get it working. I have a calculation for full year 2017 so therefore if I use the above and select the same date I should in theory end up with the same number? This isn't the case for me.

               

              My current formula is now:

               

              Sum(    {<DELETED_FLAG={'0'}, TYPE = {'SI' , 'SC'} , NOMINAL_CODE = {'4000', '4101' , '4100' , '4002' , '4003' , '4102'} ,DATE=,Year=,Month=,Quarter=,DATE={">=$(YearStart(Max(DATE)))<=$(=Max(DATE))"}  >}    NET_AMOUNT)

               

               

              Also is there a way to make this function dynamic so it is always looking at today's date YTD? Without having to actually select a date?

               

              Thanks,

               

              Nick

                • Re: YTD, MTD issue
                  omar bensalem

                  In your current expression, you have this :

                  DELETED_FLAG={'0'}, TYPE = {'SI' , 'SC'} , NOMINAL_CODE = {'4000', '4101' , '4100' , '4002' , '4003' , '4102'}


                  That means that you're only returning the Net Amount 'YTD' but also and only for the deleted Flag =0, for TYPE SI and SC and for the above nominal codes.


                  I think your second expression (for the full year) does not contain this first part?


                  for your second question, we're already forcing Qlik to take into consideration the max(date) that exists in your model.

                  If you select a date, the selected date will be the max(date); if you do not select a date, Qlik will look for the max(date) in your source and work with.

                  Now if you want it to ALWAYS return YTD for the max existing date no matter what selection you make,

                  you alter max(DATE) to max({1}DATE) which means telling QLIK to always return the max date no matter what selection we make.


                  If sthing is not clear, don't hesitate to poke me

                    • Re: YTD, MTD issue
                      Nicholas Truss

                      Hi Omar,

                       

                      Thanks for coming back to me. My full year expression is basically the same and is as follows

                       

                      Sum(    {<DELETED_FLAG={'0'}, TYPE = {'SI' , 'SC'} , NOMINAL_CODE = {'4000', '4101' , '4100' , '4002' , '4003' , '4102'} ,Year ={'$(vYear_Current)'}  >}    NET_AMOUNT)

                       

                      Just for some reason I can't get that YTD function to work.

                        • Re: YTD, MTD issue
                          omar bensalem

                          Can you please attach your QVF file?

                          • Re: YTD, MTD issue
                            Nicholas Truss

                            Hi Omar,

                             

                            Please see dummy app attached.

                             

                            Thanks again for the assistance!

                             

                            Nick

                              • Re: YTD, MTD issue
                                omar bensalem

                                Hi Nich,

                                when you create a DATE={"EXPRESSION"} as in our case, please don't leave BLANKS:

                                DATE={">=  $( =YearStart  (Max(DATE)))<=  $(=Max(DATE))    "} 



                                Because we're surronding the expression by " ", when we leave blanks, Qlik will misinterpret our expression:

                                DATE={">=$(=YearStart(Max(DATE)))<=$(=Max(DATE))"} 

                                 

                                Find your app attached, it's all working just fine

                                 

                                If your question ahs been answered, please close the thread but marking the correct answer as so !

                                Have a nice day

                                  • Re: YTD, MTD issue
                                    Nicholas Truss

                                    Hi Omar,

                                     

                                    Amazing, thanks for your time on this!!

                                     

                                     

                                    Out of interest how easy is it to then edit this expression to get previous YTD/MTD for comparisons?

                                     

                                    Nick

                                      • Re: YTD, MTD issue
                                        omar bensalem

                                        Hi again Nich,

                                         

                                        Sorry for being late, I was taking my lunch !

                                         

                                        So, if we wanna focus on the YTD-1, we wanna alter this part:

                                        DATE={">=$(=YearStart(Max(DATE)))<=$(=Max(DATE))"}


                                        to do so, we want to situate our self in the previous year, to do that, their is a function called addYears.


                                        How we use it? addYears('04/12/2017',-1) = 04/12/2016;


                                        So our expression will become:

                                        DATE={">=$(=YearStart(addYears(max(DATE),-1)))<=$(=addYears(max(DATE),-1))"}


                                        With this, if the max(Date) in 2017 is 24/03/2017 (like in your case)

                                        The YTD-1 will return the NET_AMOUNT from 01/01/2016 to 24/03/2016 .


                                        See the attached app


                                        You'll find YTD, YTD-1

                                        MTD, MTD-1


                                        Your max(DATE) = 24/03/2017

                                        Create a filter date, try to change your date and see how your expression will change to become from the year/month start to the selected date.


                                        As of your expression using vCurrentYear, when you select a date, it wil return the Net Amount of only the selected date


                                        Capture.PNG