Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

omnomnom
New 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 experimentsSmiley Happy

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

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

Why dont just below expressions

1) FirstSortedValue(Stock, Date)  weekstart

2) FirstSortedValue(Stock, -Date)  weekend

omnomnom
New Contributor

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

Set analysis faster then FirstSortedValue()

especially on large data-sets.

MVP
MVP

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

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

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

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

omnomnom
New Contributor

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

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





MVP
MVP

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

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

Community Browser