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

[Anonymous]

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.

Time	Product	Items Sold
12:30 PM	Bananas	10
12:58 PM	Bananas	15
1:34 PM	Bananas	8
2:01 PM	Bananas	15
2:30 PM	Bananas	10
3:01 PM	Bananas	15

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!

[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

[Anonymous]

Thanks for the input! It's definitely a unique requirement. I was trying to avoid any level of precalculation though. Ideally this would be flexible, so that it wouldn't have to be just an hour, but maybe 2 hours, or an hour and a half, etc.