Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Looking for an expression to solve conditional counting issue

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_DTOUT OF STK FLGOUT OF STK DAYSSTOCKOUT FILTERIN STK FLGCODE
2011-05-3011081064582
2011-05-3111091064582
2011-06-0111101064582
2011-06-0211111064582
2011-06-0311121064582
2011-06-0411120064582
2011-06-0511120064582
2011-06-0611131064582
2011-06-0711141064582
2011-06-0811151064582
2011-06-0911161064582
2011-06-1011171064582
2011-06-1111170064582
2011-06-1211170064582
2011-06-1311181064582
2011-06-1411191064582
2011-06-1511201064582
2011-06-1611211064582
2011-06-1711221064582
2011-06-1811220064582
2011-06-1911220064582
2011-06-2011231064582
2011-06-2111241064582
2011-06-2211251064582
2011-06-2311261064582
2011-06-2411271064582
2011-06-2511270064582
2011-06-2611270064582
2011-06-2711281064582
2011-06-2811291064582
2011-06-2911301064582
2011-06-3011311064582
2011-07-0111321064582
2011-07-0211320064582
2011-07-0311320064582
3525
3 Replies
christian77
Partner - Specialist
Partner - Specialist

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.

SunilChauhan
Champion II
Champion II

please the attached file

hope this helps

Sunil Chauhan
Not applicable
Author

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.