2 Replies Latest reply: Feb 12, 2017 5:26 PM by Dave Bauer RSS

    Include 0 value dates between min and max days

    Dave Bauer

      We are using Qlik Sense Server version 2. 

       

      I am looking to include all 0 value dates on a bar chart between the min and max day of something occurring.  I will try to explain the situation as best as I can.

       

      We have a fact table listing out all open quotes, open orders, and invoices.  We identify whether it is on of those in a field called SalesTransactionType.  Each type of transaction has a date key that connects to its designated date dimension table with the various dates, years, months, weeks, etc. in that table.

       

      The issue I am having is that we want to see a bar chart with all days, including days with no values, but set dynamically between the first occurrence of an Open Order and the last occurrence.  The following expression gives me the first part, with all dates with zero, but does not limit the range:


      sum ({$< SalesTransactionType = {'Open Order'}>} SalesTopLine)

      +

      sum({$}0)


      Our date dimension table includes all dates from 2012-2017.  So our bar chart goes from 2012-2017.  What I want to be able to do is shrink that bar chart down dynamically to only the first date of an Open Order in our table through the last date of an Open Order in our table.  The two tables are kind of structured like this:

       

      SalesTransactionTypeQuoteDateOrderDateInvoiceDateSalesTopLine
      Open Quote12-01-1610.23
      Open Order12-02-1613.55
      Invoice1-13-17152.65
      Open Order1-22-1722.75

       

      OrderDateYearMonthYearMonthDay
      12-02-162016122016-Dec2
      12-03-162016122016-Dec3

       

      Does anyone have a suggestion on how to do this? 

       

      Thanks y'all!

        • Re: Include 0 value dates between min and max days
          Ruben Marin

          Hi David, try with:

          sum ({$<OrderDate={">=$(=date(Min({<SalesTransactionType = {'Open Order'}>} OrderDate)))<=$(=date(Max({<SalesTransactionType = {'Open Order'}>} OrderDate)))"}>} SalesTopLine)

           

          Set analysis applied to the field used as dimension, and retrieving the min and max values for that field with SalesTransactionType='Open Order'.

           

          Hope this helps.

            • Re: Include 0 value dates between min and max days
              Dave Bauer

              So close, but you totally got me there.  Thank you so much.  I ended up with this expression:

               

              sum ({$<SalesTransactionType = {'Open Order'}, OrderRequiredDateKey={">=$(=date(Min({<SalesTransactionType = {'Open Order'}>} OrderRequiredDateKey)))<=$(=date(Max({<SalesTransactionType = {'Open Order'}>} OrderRequiredDateKey)))"}>} SalesTopLine)

              + sum({$<OrderRequiredDateKey={">=$(=date(Min({<SalesTransactionType = {'Open Order'}>} OrderRequiredDateKey)))<=$(=date(Max({<SalesTransactionType = {'Open Order'}>} OrderRequiredDateKey)))"}>}0)

               

              It appears to be working perfectly.  Thank you again! 

               

              Aloha,

               

              Dave