Aggregate condition

[geogou1973]

Hello,

I have the following problem.

In the first table i have the stock items that are <> 0 per Store and Secteur

with the expression count(if(STOC<>0,ITEM_ID))

Store	Secteur	Stock<> 0
D02	10	2.111
D02	20	332
D02	30	697

in the second table i have the same expression per store type with all the stores that include in this store type and secteur totally for this store type

Store Type	Secteur	Stock <> 0
FRESH	10	84.425
FRESH	20	12.223
FRESH	30	22.320

I want in the first table to bring another expression with the numbers of the second table totally per store and secteur like the following table

Store	Secteur	Stock<> 0	Stock<> 0 per Store Type
D02	10	2.111	84.425
D02	20	332	12.223
D02	30	697	22.320

I created the expression 

aggr(count (if(STOC<>0,ITEM_ID)),STORE_TYPE,SECTEUR) but the formula does not fill all the rows.

Fills only one store , one secteur with the numbers of three secteurs

Has anyone an idea how can i fill the column correctly ?

Thank you in advance

[StarinieriG]

Have you tried to use sum outside that expression?

sum(aggr(count (if(STOC<>0,ITEM_ID)),STORE_TYPE,SECTEUR))

[geogou1973]

I have the same result. Now fills the - with 0 

[StarinieriG]

I think that it happens because of if, maybe set analysis will be the best solution

[sunny_talwar]

Try this

Aggr(NODISTINCT 
    Count(If(STOC <> 0, ITEM_ID))
, STORE_TYPE, SECTEUR)

[geogou1973]

how can i convert the folowind to set analysis expression

count (if(STOC<>0,ITEM_ID))

[geogou1973]

It works. 

The problem i have now is that when i select a store the numbers changes to the stock items <>0 that the  store have. How can i avoid this ?

[sunny_talwar · Accepted solution]

May be this

Aggr(NODISTINCT 
    Count({<STORE>}If(STOC <> 0, ITEM_ID))
, STORE_TYPE, SECTEUR)

[sunny_talwar]

or even this

Aggr(NODISTINCT 
    Count({<STORE, STOC = {"<> 0"}>}ITEM_ID)
, STORE_TYPE, SECTEUR)

[geogou1973]

It works.

Thank you.