Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

danalvarez
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
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:

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

danalvarez
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.

Community Browser