Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
];
Why dont just below expressions
1) FirstSortedValue(Stock, Date) weekstart
2) FirstSortedValue(Stock, -Date) weekend
Set analysis faster then FirstSortedValue()
especially on large data-sets.
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 |
Set analysis is not always faster with complex aggregation function like this which you have used. It's confusing as well.
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
Have you considered creating flag fields in the script for the start and end dates per week?