topic Re: Set analysis help in QlikView

Set analysis help

dafnis14 — Sun, 08 Feb 2015 09:20:06 GMT

Hi,

I would appreciate some help with set analysis for the following case:

2 data tables:

- Inventory data with the fields SupCode, Inventory
- Sales Data with the fields Sales_Date, Sales

A straight table with the dimensions:

- Supplier Code
- Supply Date

For each supply date, the table should show total inventory for the Supply Date, and total sales for a period of 30 days previous to that Supply date.

To calculate the sales, I wrote the following expression:

aggr(sum({$<Sales_Date= {">$(= date(Supply_Date -30)) <=$(=date(Supply_Date))"}>} Sales), SupCode)

But it works only if a certain supply date is selected. Otherwise, null values are displayed.

Can someone assist me?

Thanks!

Re: Set analysis help

Peter_Cammaert — Sun, 08 Feb 2015 09:41:23 GMT

A set in set analysis is calculated only once, at the start of chart recalculation and not row-by-row.

You can use an AsOf table to solve this problem. See this document Calculating rolling n-period totals, averages or other aggregations for an explanation of why set analysis doesn't work and how AsOf tables can solve most of these rolling period-related problems.

Best,

Peter

Re: Set analysis help

dafnis14 — Sun, 08 Feb 2015 10:30:06 GMT

Great!

Thanks!