topic Re: Set analysis aggr max min dates. bug or not? in QlikView

Set analysis aggr max min dates. bug or not?

omnomnom — Thu, 19 May 2016 06:29:45 GMT

Hi! recently i noticed strange behavior with " {<DATE={"=aggr(min(DATE),WEEK)"}>} " expression in QV 12 sr2.

i tried to make a table with stock values on begin and and of the weeks like below

but instead of this:

YEAR	SKU	WEEK	stock week start	stock week end
			913	913
2016	Q	18-2016	286	286
2016	Q	19-2016	284	174
2016	Q	20-2016	174	171
2016	Q	21-2016	169	168

i got this:

YEAR	SKU	WEEK	stock week start	stock week end
			913	913
2016	Q	18-2016	286	286
2016	Q	19-2016	284	284
2016	Q	20-2016	174	174
2016	Q	21-2016	169	169

same numbers in 2 columns.

i noticed that two different expressions

max({<DATE={"=aggr( MAX (DATE),WEEK)"}>}DATE)

min({<DATE={"=aggr( MIN (DATE),WEEK)"}>}DATE)

gave me same results like on table below:

WEEK	YEAR	SKU	max({<DATE={"=aggr(min(DATE),WEEK)"}>}DATE)	min({<DATE={"=aggr(max(DATE),WEEK)"}>}DATE)
			16.05.2016	01.05.2016
18-2016	2016	Q	01.05.2016	01.05.2016
19-2016	2016	Q	02.05.2016	02.05.2016
20-2016	2016	Q	09.05.2016	09.05.2016
21-2016	2016	Q	16.05.2016	16.05.2016

where am i wrong or miss something?

raw data for your experiments:)

LOAD * INLINE [

YEAR, WEEK, DATE, STOCK,SKU

2016, 18-2016, 01.05.2016, 286,Q

2016, 19-2016, 02.05.2016, 284,Q

2016, 19-2016, 03.05.2016, 283,Q

2016, 19-2016, 04.05.2016, 283,Q

2016, 19-2016, 05.05.2016, 282,Q

2016, 19-2016, 06.05.2016, 176,Q

2016, 19-2016, 07.05.2016, 176,Q

2016, 19-2016, 08.05.2016, 174,Q

2016, 20-2016, 09.05.2016, 174,Q

2016, 20-2016, 10.05.2016, 173,Q

2016, 20-2016, 11.05.2016, 173,Q

2016, 20-2016, 12.05.2016, 173,Q

2016, 20-2016, 13.05.2016, 173,Q

2016, 20-2016, 14.05.2016, 172,Q

2016, 20-2016, 15.05.2016, 171,Q

2016, 21-2016, 16.05.2016, 169,Q

2016, 21-2016, 17.05.2016, 168,Q

2016, 21-2016, 18.05.2016, 168,Q

];

Re: Set analysis aggr max min dates. bug or not?

Kushal_Chawda — Thu, 19 May 2016 13:00:40 GMT

Why dont just below expressions

1) FirstSortedValue(Stock, Date) weekstart

2) FirstSortedValue(Stock, -Date) weekend

Re: Set analysis aggr max min dates. bug or not?

omnomnom — Thu, 19 May 2016 13:06:41 GMT

Set analysis faster then FirstSortedValue()

especially on large data-sets.

Re: Set analysis aggr max min dates. bug or not?

swuehl — Thu, 19 May 2016 14:08:53 GMT

Set analysis is not always faster, especially if you are using quite complex advanced searches.

You may want to compare kushals expressions to yours with your data to get some numbers.

(Would be interesting to see here in the forum).

With your expressions (which I wouldn't use), try

max({<DATE={"=aggr(NODISTINCT MAX (DATE),WEEK)"}>}DATE)

min({<DATE={"=aggr(NODISTINCT MIN (DATE),WEEK)"}>}DATE)

WEEK	YEAR	SKU	max({<DATE={"=aggr(NODISTINCT MAX (DATE),WEEK)"}>}DATE)	min({<DATE={"=aggr(NODISTINCT MIN (DATE),WEEK)"}>}DATE)
			18.05.2016	01.05.2016
18-2016	2016	Q	01.05.2016	01.05.2016
19-2016	2016	Q	08.05.2016	02.05.2016
20-2016	2016	Q	15.05.2016	09.05.2016
21-2016	2016	Q	18.05.2016	16.05.2016

Re: Set analysis aggr max min dates. bug or not?

Kushal_Chawda — Thu, 19 May 2016 14:11:57 GMT

Set analysis is not always faster with complex aggregation function like this which you have used. It's confusing as well.

Re: Set analysis aggr max min dates. bug or not?

omnomnom — Thu, 19 May 2016 15:29:22 GMT

aggr NODISTINCT didn`t work properly because

its pull all dates in week

and you get incorrect results in

expression like this:

sum({<DATE={"=aggr(NODISTINCT min(DATE),WEEK)"}>}STOCK)

my goal get correct values on start and end of the weeks.

what about FirstSortedValue(Stock, Date) ?

i will try to compare both expressions later

Re: Set analysis aggr max min dates. bug or not?

swuehl — Thu, 19 May 2016 16:09:18 GMT

Have you considered creating flag fields in the script for the start and end dates per week?