2 Replies Latest reply: Aug 6, 2018 1:50 PM by Tracey Curran RSS

    Selecting dates before the date selected

    Tracey Curran

      Hi

       

      I'm not sure if this can be done and I am very new to Qlik Sense so I would really appreciate someone explaining this to me dummy style

       

      In my data I have a list of businesses my company owns and a conversion date to our PMS system [DBList Converted Date] if my user selects a [DBList Converted Date] I want the selection in this field to include all dates equal to or less than this date.  So all companies converted on or before this date. This list isn't extensive see below but it will be added to as more conversions happen.  I don't want this date to affect my data because in my charts and KPI's I want to see data after that date.  I'm basically trying to look at the underlying trends of the business without the affects of acquisitions or conversions.  I found some answers online which tell me to create a variable using Let vConvertdate = GetFieldSelections([DBList Converted Date],'","') but I'm not sure where to create this or even if I've got the formatting right.

       

      DBList Converted Date
      01/10/2015
      01/04/2016
      01/08/2016
      01/07/2016
      01/04/2017
      01/05/2017
      01/02/2017
      01/01/2017
      01/05/2016
      01/11/2016
      01/03/2016
      01/07/2017

       

      I hope this makes sense, any help would be much appreciated.

       

      Thanks

       

      Tracey

        • Re: Selecting dates before the date selected
          Bala Bhaskar

          May be like this:

           

          Let vConvertdate = GetFieldSelections([DBList Converted Date],'","');

           

          In Expressions need to use the above variable as like: $(vConvertdate)

           

          If, user selected date as: 08/01/18, user want to see data, suppose sales after this date.

           

          Sum({<[DBList Converted Date] >= {"=$(vConvertDate)"}>}Sales)

          • Re: Selecting dates before the date selected
            Tracey Curran

            Thanks but this doesn't, but I think it's because I'm trying to make selections based on a date and I'm not sure if that is possible. 

             

            I want to include all businesses with a conversion date on or before the date selected, the business conversion date is held in a table called dtblist_table which is just a list of all business in the app with various master data (eg the region, director responsible, etc.)  I'm using the available dates from this table in a drop down but when I select one I want it to select Business Names who where converted on that date or before it. I then I want to calculate all sales for businesses selected.