Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create an expression but i have no idea, if i count the OUT_OF_STK_FLG i get 35, but I don't want count the flag if the OUT OF STOCK DAYS has not changed, in other words than this value is redundant. The result should be 25 instead of 35.
I added an additional column STOCKOUT FILTER and added manually the values and made a SUM, so you can see which row should not be counted for OUT OF STK FLG.
Any Ideas? I am thing of AGGR function, but how?
DSS_DATE.DAY_DT | OUT OF STK FLG | OUT OF STK DAYS | STOCKOUT FILTER | IN STK FLG | CODE |
2011-05-30 | 1 | 108 | 1 | 0 | 64582 |
2011-05-31 | 1 | 109 | 1 | 0 | 64582 |
2011-06-01 | 1 | 110 | 1 | 0 | 64582 |
2011-06-02 | 1 | 111 | 1 | 0 | 64582 |
2011-06-03 | 1 | 112 | 1 | 0 | 64582 |
2011-06-04 | 1 | 112 | 0 | 0 | 64582 |
2011-06-05 | 1 | 112 | 0 | 0 | 64582 |
2011-06-06 | 1 | 113 | 1 | 0 | 64582 |
2011-06-07 | 1 | 114 | 1 | 0 | 64582 |
2011-06-08 | 1 | 115 | 1 | 0 | 64582 |
2011-06-09 | 1 | 116 | 1 | 0 | 64582 |
2011-06-10 | 1 | 117 | 1 | 0 | 64582 |
2011-06-11 | 1 | 117 | 0 | 0 | 64582 |
2011-06-12 | 1 | 117 | 0 | 0 | 64582 |
2011-06-13 | 1 | 118 | 1 | 0 | 64582 |
2011-06-14 | 1 | 119 | 1 | 0 | 64582 |
2011-06-15 | 1 | 120 | 1 | 0 | 64582 |
2011-06-16 | 1 | 121 | 1 | 0 | 64582 |
2011-06-17 | 1 | 122 | 1 | 0 | 64582 |
2011-06-18 | 1 | 122 | 0 | 0 | 64582 |
2011-06-19 | 1 | 122 | 0 | 0 | 64582 |
2011-06-20 | 1 | 123 | 1 | 0 | 64582 |
2011-06-21 | 1 | 124 | 1 | 0 | 64582 |
2011-06-22 | 1 | 125 | 1 | 0 | 64582 |
2011-06-23 | 1 | 126 | 1 | 0 | 64582 |
2011-06-24 | 1 | 127 | 1 | 0 | 64582 |
2011-06-25 | 1 | 127 | 0 | 0 | 64582 |
2011-06-26 | 1 | 127 | 0 | 0 | 64582 |
2011-06-27 | 1 | 128 | 1 | 0 | 64582 |
2011-06-28 | 1 | 129 | 1 | 0 | 64582 |
2011-06-29 | 1 | 130 | 1 | 0 | 64582 |
2011-06-30 | 1 | 131 | 1 | 0 | 64582 |
2011-07-01 | 1 | 132 | 1 | 0 | 64582 |
2011-07-02 | 1 | 132 | 0 | 0 | 64582 |
2011-07-03 | 1 | 132 | 0 | 0 | 64582 |
35 | 25 |
Hi, try the next:
Count ([STOCKOUT FILTER])
or
Count({$<[STOCKOUT FILTER]={‘1’}>} [OUT OF STK FLG]) --> Best
Or
Count(if([STOCKOUT FILTER]=’1’, [OUT OF STK FLG]))
I recommend you to open the help page and look for Set Analysis
When loading, in order to have your flag column:
TABLE:
LOAD
[OUT OF STK FLG],
……………………..
IF(PREVIOUS([OUT OF STK FLG])= [OUT OF STK FLG],’0’,’1’)) AS "STOCKOUT FILTER",
FROM … origin
Order by DSS_DATE.DAY_DT
You may correct syntax.
please the attached file
hope this helps
The STOCKOUT_FILTER value was added from me and was a helper to understand the problematic. This value is not available, so we cannot use it.
The solution withe the PREVIOUS is a good idea as long as the sorting is correct. In the meantime I found a different solution, the fact is that I may calculate the max(OUT OF STK DAYS) minus the min(OUT OF STK DAYS) + 1 if [OUT OF STK FLG]=1.
Thanks anyway for your help.