topic Re: Looking for an expression to solve conditional counting issue in QlikView

Looking for an expression to solve conditional counting issue

Mon, 29 Aug 2011 09:49:23 GMT

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

Re: Looking for an expression to solve conditional counting issue

christian77 — Mon, 29 Aug 2011 10:10:24 GMT

Hi, try the next:

Count ([STOCKOUT FILTER])

Count({$<[STOCKOUT FILTER]={‘1’}>} [OUT OF STK FLG]) --> Best

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.

Re: Looking for an expression to solve conditional counting issue

SunilChauhan — Mon, 29 Aug 2011 10:22:13 GMT

please the attached file

hope this helps

Re: Looking for an expression to solve conditional counting issue

Mon, 29 Aug 2011 11:19:14 GMT

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.