4 Replies Latest reply: Oct 10, 2018 11:23 AM by Vern Jones RSS

    How to make YTD Analysis ?

    Siraj Ahamed Saffiulla

      Hello All,

       

      I have data on monthly basis from 01-2016 to 12-2022 in that need to create YTD analysis which to provide result like below

       

      In Month of October 2018:

      Result: From Jan 2018 to SEP 2018

       

      In Month of January 2019:

       

      Result : From Jan 2018 to Dec 2018

       

      In Month of March 2019:


      Result: From Jan 2019 to Feb 2019

       

       

      Currently I am using current Year and upto Previous month filter in my expression,

      but when Jan 2019 comes it shows 0 in Chart because of filter.

       

      My Expression

       

       

      (count(distinct  {<[Project Status]={'In Progress','Complete'},SOP_YEAR={$(vCurrentYear)},SOP_MONTH={"<=$(vPrevMonthNum)"}>} FLM_ProjectNumber)

       

       

       

       

      Please help me to achieve this.

       

      Thanks in Advance

      Siraj

        • Re: How to make YTD Analysis ?
          omar bensalem

          Please refer to this thread :

           

          YTD, MTD issue

          • Re: How to make YTD Analysis ?

            Hii... I am newbie here,I am having an issue in creating a YTD analysis. I don't know, How to do YTD analaysis. I have to complete the analysis for my office and I am unaware about this term. Maybe any expert can help me. I request here please guide me regarding this.

              • Re: How to make YTD Analysis ?
                omar bensalem

                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: How to make YTD Analysis ?
                Justin Dallas

                In addition to the recommendation by Omar, you also might want to check into using a Master Calendar.

                 

                Creating A Master Calendar

                 

                Generally, a dashboard will have one main Master Calendar, but you can still find value in flagging your Projects start/cancel/complete dates.