25 Replies Latest reply: Sep 14, 2018 2:58 PM by Camron Allan RSS

    YTD based on Selected Month

    Rohit Thete

      Hello,

       

      I am trying to create YTD sum of revenue using a Date field (EffectiveDate) in the data set.

       

      I tried many expressions posted in community but wasn't able to generate YTD based on month selection.

       

      What I am looking for:

       

      If Feb is selected then sum of revenue for Jan + Feb with a condition of BType = 'New Business'

       

      Do I need to generate master calendar to get this done or it can be done based on a date field?

       

      Any help would be really appreciated.

       

      Thank you

        • Re: YTD based on Selected Month
          Mohammed Mukram Ali

          Hi,

           

           

          Qlik Sense App: Prior Period Comparison with Set Analsysis

           

           

          Follow the above the link.

          You will find example.

          • Re: YTD based on Selected Month
            Justin Dallas

            Master Calendar might help.  The best thing I can see an MC doing is that you could have a 'Current Year' flag.  And then you could do something to the effect of

             

            PSEUDO Set Analsysis i.e It probably won't parse correctly.

             

            SUM ({<[Current Year]=1, EffectiveDate="{=$(=< MAX(Effective Date)}">} Money)
            

             

            There may be a cleaner and more clever way to get what you are looking for, but this is off the top of my head.

            • Re: YTD based on Selected Month
              Rangam Seshadri

              Try this

               

              It will generate current year (Selected Year in the filter else maximum year in the data) YTD Sales

               

              sum({<Year_Policy_Eff_and_Acct_Eff_Merged={"$(=Max(Year_Policy_Eff_and_Acct_Eff_Merged))"},EffectiveDate={"<=$(=Max(EffectiveDate))"},Month=>}TotalRevenue_INV)

               

              Previous Year YTD Sales (Till Same month and Date):

               

              sum({<Year_Policy_Eff_and_Acct_Eff_Merged={"$(=Max(Year_Policy_Eff_and_Acct_Eff_Merged)-1)"},EffectiveDate={"<=$(=AddYears(Max(EffectiveDate),-1))"},Month=>}TotalRevenue_INV)

               

              Note: In case, if you have any date filters which are affecting the data then nullify those filters as well in the set expression.

                • Re: YTD based on Selected Month
                  Rohit Thete

                  Thanks Rangam.

                   

                  In the above expressions, the year selected in the filter pane will be considered as current year?

                   

                  What will be the expression for current year MTD and Previous year MTD based on select month?

                   

                  What I am looking for is:

                   

                  Current year MTD, Previous Year MTD, YTD and Previous YTD.

                   

                  example: if Feb is selected then revenue for Feb this year, previous year, YTD revenue, Previous YTD revenue

                   

                  Thanks for your help

                • Re: YTD based on Selected Month
                  Camron Allan

                  These types of time-related set expressions can get so messy.

                   

                  You should try QlikRTP!  It's free.

                  You can read about it in my post, here.

                   

                  RTP stands for Relative Time Periods.  This script leverages your existing month dimension to add the following relative time elements to your app:

                  • To-Date ranges such as YTD and QTD
                  • Relative time periods such as "Previous Year", "Previous Month", or “same period last year”
                  • Rolling periods which are used for moving averages. (Ex. "Rolling 3 month average")
                  • Year-over-Year and MoM growth metrics

                   

                  Not only that, these RTPs will work for any anchor month! (not just the current period)

                  This approach greatly simplifies your measures by only requiring two set modifiers.