2 Replies Latest reply: Feb 1, 2018 9:35 AM by Daniel Alvarez RSS

    Can a chart expression reference previous row data (but with a specific criteria)?

    Daniel Alvarez

      Here's a contrived example to help illustrate what I want to do. Imagine you have a table that stores a time and a count of how many items of a particular product sold at that time. So you have a table like the following. Pretend I've already scoped it down to just Bananas as the product sold, but there would be other products.

       

      TimeProductItems Sold
      12:30 PMBananas10
      12:58 PMBananas15
      1:34 PMBananas8
      2:01 PMBananas15
      2:30 PMBananas10
      3:01 PMBananas15

       

      What I want to do is find the time where, during an hour ending at a Time value, the most number of items were sold per product type. So in this case at 12:30, the hour before that (from 11:30-12:30), 10 bananas were sold (just 10 at 12:30). At 12:58, 25 bananas were sold in the previous hour (11:58-12:58), etc.

       

      In this case, in the hour range 2:01-3:01 40 bananas were sold. So I need to be able to report that 3:01 PM was the most productive hour for bananas, selling 40 bananas. And I would need to do this for each product.

       

      However, as you can see, this relies on grouping the data by product, and then adding up a single column, but only considering columns that match a certain criteria. This also has an implied order involved.

       

      Is there anyway that Qlik could handle this situation with the table as is? Or do I need to maybe do a bunch of precalculating and store that in the database right away?

       

      Thanks!

        • Re: Can a chart expression reference previous row data (but with a specific criteria)?
          Juraj Misina

          Hi Daniel,

          the requirement sounds pretty wild and I don't think you can do that in chart expressions (at least not easily). What you need basically is an hourly total for every time/product combination. I would precalculate that in script in a following way:

          1) Create a table with distinct Time/Product combinations:

          Tmp_Intervals:
          LOAD Distinct
               Product,
               Time
          From Fact_Table;
          
          

           

          2) Join 1 hour intervals to those combinations

          Left Join(Tmp_Intervals)
          IntervalMatch(Time, Product)
          LOAD Distinct
               Time-(1/24)   as StartTime,
               Time          as StopTime,
               Product
          From Fact_Table;
          
          

           

          3) Join respective quantities to those intervals:

          Left Join(Tmp_Intervals)
          LOAD
               Time,
               Product,
               Quantity
          From Fact_Table;
          
          

           

          4) Sum up quantities per interval

          Tmp_Interval_Quantities:
          LOAD
               Product,
               StartTime,
               StopTime,
               Sum(Quantity)     as HourlyQuantity
          From Fact_Table
          Group By
               Product,
               StartTime,
               StopTime
          ;
          
          

           

          5) Merge this back to the fact table (perhaps using mapping load and applymap based on Product, Time and Time-(1/24))

          6) In your chart you could then use HourlyQuantity field for your purpose.

           

          Hope this helps.

          Juraj