2 Replies Latest reply: Mar 9, 2018 12:27 PM by Samuel Washburne RSS

    Set expression with date range using Today()

    Samuel Washburne

      Qlik community,

       

      I am trying to create a more dynamic range for the set expression below using the today() function but everything I have tried has not worked. Essentially, I want to return all the OrderID's for FY2018 from the beginning of the Fiscal Year (10/1/2017) up until the date that the app is opened. The work around has been to simply type in the actual month each time we move into the next month but I want this expression to basically update itself.

       

      RangeSum (above(count({$<[ORDERID]={*}>*$<FYear={'FY2018'}>*$<Month={'Oct', 'Nov','Dec','Jan'}>} [ORDERID]),0,RowNo()))

       

      Please help!

       

      Thanks!

      Ben mto

        • Re: Set expression with date range using Today()
          Sunny Talwar

          I would rather use the data field to do this

           

          RangeSum(Above(Count({$<DateField = {"$(='>=' & YearStart(Today(), 0, 10) & '<=' & Date(Today()))"}, FYear, Month>} [ORDERID]), 0, RowNo()))

           

          Where ='>=' & YearStart(Today(), 0, 10) & '<=' & Date(Today()) in a KPI object should show you the range you need and in the format you have your date field.

            • Re: Set expression with date range using Today()
              Samuel Washburne

              Sunny,

               

              Sorry for the late response and thanks for the suggestion.

               

              I was able to solve part of my problem using the following expression:

               

              Measure 1:

              RangeSum (above(count({$<DateField ={'>=$(=Date(vFY2017Start))<$(=Date(AddYears(Today(),-1)))'}>} [OrderID]),0,RowNo()))

               

              Measure 2:

              RangeSum (above(count({$<DateField ={'>=$(=Date(vFY2018Start))<$(=Date(Today()))'}>} [OrderID]),0,RowNo()))

               

              However, these two rangesum "Trendlines" need to track against a static Trendline that has a set number of targets per month e.g. Note: These numbers do not need to aggregate - they are hard targets per month.

               

              *Measure 3*

               

              Month     TargetOrders

              Oct,              15

              Nov,              30

              Dec,             45

              Jan,              60

              etc, etc.

               

              If I put measures 1 and 2 in without the static trendline, everything works as it should and the line chart populates from October to March (the current month of this posting). However, if I add measure 3, there is no good way to make it only show the data points as defined by a date range. At least, I can't figure out the set expression that will make it work and I have tried a ton.

               

              Can you help?

               

              -Ben