# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor

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

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!

2 Replies
Luminary

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

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:
Product,
Time
From Fact_Table;

```

2) Join 1 hour intervals to those combinations

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

```

3) Join respective quantities to those intervals:

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

```

4) Sum up quantities per interval

```Tmp_Interval_Quantities:
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

New Contributor

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

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.