Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
omnomnom
Contributor
Contributor

Set analysis aggr max min dates. bug or not?

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
2016Q18-2016286286
2016Q19-2016284174
2016Q20-2016174171
2016Q21-2016169168

i got this:

YEAR SKU WEEK stock week start stock week end
913 913
2016Q18-2016286286
2016Q19-2016284284
2016Q20-2016174174
2016Q21-2016169169

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-20162016Q01.05.201601.05.2016
19-20162016Q02.05.201602.05.2016
20-20162016Q09.05.201609.05.2016
21-20162016Q16.05.201616.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

];

6 Replies
Kushal_Chawda

Why dont just below expressions

1) FirstSortedValue(Stock, Date)  weekstart

2) FirstSortedValue(Stock, -Date)  weekend

omnomnom
Contributor
Contributor
Author

Set analysis faster then FirstSortedValue()

especially on large data-sets.

swuehl
MVP
MVP

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-20162016Q01.05.201601.05.2016
19-20162016Q08.05.201602.05.2016
20-20162016Q15.05.201609.05.2016
21-20162016Q18.05.201616.05.2016
Kushal_Chawda

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

omnomnom
Contributor
Contributor
Author

aggr NODISTINCT didn`t work properly because

its pull all dates in week

1.png

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





swuehl
MVP
MVP

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