topic Re: Basic Set Analysis Question in QlikView

Basic Set Analysis Question

Tue, 29 Nov 2011 09:33:30 GMT

Hi,

I would like to turn the following transaction table...

Article	Date	Transaction	Value
A	3-1-2011	1	10
A	17-1-2011	2	20
A	14-2-2011	3	10
A	13-3-2011	4	60
A	18-4-2011	5	70
A	19-4-2011	6	30
A	22-6-2011	7	50
A	18-8-2011	8	20
A	31-8-2011	9	10
A	22-9-2011	10	60
A	1-11-2011	11	90
B	14-2-2011	12	11
B	17-2-2011	13	22
B	15-4-2011	14	44
B	17-5-2011	15	33
B	31-5-2011	16	88
B	29-6-2011	17	55
B	31-7-2011	18	66
B	18-8-2011	19	99
B	11-10-2011	20	23

...into YTD accumulation of 'Value' over 'Article' and 'Month'. Something like this...

Article	Month	YTD	Month end
		441	30-11-2011
A	Jan	30	31-1-2011
A	Feb	40	28-2-2011
A	March	100	31-3-2011
A	Apr	200	30-4-2011
A	Juni	250	30-6-2011
A	Aug	280	31-8-2011
A	Sep	340	30-9-2011
A	Nov	430	30-11-2011
B	Feb	33	28-2-2011
B	Apr	77	30-4-2011
B	May	198	31-5-2011
B	Juni	253	30-6-2011
B	Juli	319	31-7-2011
B	Aug	418	31-8-2011
B	Oct	441	31-10-2011

Can this be accomplished by Set Analysis?

the following syntax does not work:

Sum({<Date = {'>=$(=YearStart(aggr(NODISTINCT Max({1} Date),Article,Month)))} <= $(=aggr(NODISTINCT  Max({1} Date),Article,Month))'}>}Value)

- I know it's also possible to achieve the same goal using YTD flags in the script but in this case, i rather not.

- attached .qvw as example

appreciate your help!

Best regards,

Dror

Re: Basic Set Analysis Question

Miguel_Angel_Baeyens — Tue, 29 Nov 2011 09:48:40 GMT

Hi Dror,

Check the attached application. I'm using RangeSum() function and Above() in a new straight table.

RangeSum(Above(Sum(Value), 0, NoOfRows()))

Hope that helps.

Miguel

Re: Basic Set Analysis Question

Tue, 29 Nov 2011 10:03:45 GMT

Miquel,

Thanks! (again).

This, definitly answer my problem.

However, i'm curius wether it's possible to use Set Analysis to aggregate value beyond the dimmension selection (month).

Dror

Re: Basic Set Analysis Question

Miguel_Angel_Baeyens — Tue, 29 Nov 2011 10:10:58 GMT

Hi Dror,

Not with your model. This is a very recurring question in the QlikCommunity, you can find it also named rolling months. There are several solutions, the most accepted purportedly is to create a table in the script AsOf for each possible date you have in your model. Anyway, using this method is usually cleaner and don't require to create that table in the script, and this is one of the main uses of RangeSum() or range functions.

Hope that makes sense.

Miguel

EDIT: It's has been discussed thoroughly in the QlikCommunity as well, and might worth noting here that set analysis is not "good" for this kind of loops, since the match needs to be done in a row per row basis (or dimension value by dimension value basis), while set analysis is evaluated once and for the whole chart. Aggr() function doesn't work within a set analysis, and Max(Date) within set analysis will return always the maximum date for the whole chart, regardless the Article or any other dimensions.

Re: Basic Set Analysis Question

Tue, 29 Nov 2011 11:35:59 GMT

Miguel,

To be honest, I've posted the YTD as example for a bigger problem in my production environment.

I was asked to produce the following overview in a pivot chart:

Store	Month	Qty sold	Inventory (end of period)
Paris	Jan	20	450
Paris	Feb	15	445
...	...	...	...

- Qty sale and inventory stand are located on the same Transaction table

- Transaction table contains 6.5 million records

- Transaction table is binded to MasterCalendar by 'Date' field

- Qty sold expression is quite straightforward: Sum(QtySold)

- Inventory calculation is quite more difficult:

Each record in the transacion table contain 'Date' and 'To Date'
Inventory (January) = sum of 'Inventory' where 'Date ' >= 31-1-2011 <= 'Date To'
please bear in mind that there could be transactions that occur prior to January that fulfill this condition (This is b.t.w. why i was interested in expansion of selection using set analysis)

I''ve tried anything from IF statement, Set Analysis, Date island, crazy intervalMatch (25M records...) etc...It's either don't work or too much resources intensive.

Any idea how i can solve this?

Best regards,

Dror