topic Re: Aggregate condition in QlikView

Aggregate condition

geogou1973 — Sat, 16 Nov 2024 02:02:09 GMT

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

Re: Aggregate condition

StarinieriG — Tue, 05 Nov 2019 14:45:24 GMT

Have you tried to use sum outside that expression?

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

Re: Aggregate condition

geogou1973 — Tue, 05 Nov 2019 14:55:12 GMT

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

Re: Aggregate condition

StarinieriG — Tue, 05 Nov 2019 14:59:51 GMT

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

Re: Aggregate condition

sunny_talwar — Tue, 05 Nov 2019 15:03:57 GMT

Try this

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

Re: Aggregate condition

geogou1973 — Tue, 05 Nov 2019 15:04:16 GMT

how can i convert the folowind to set analysis expression

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

Re: Aggregate condition

geogou1973 — Tue, 05 Nov 2019 15:08:38 GMT

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 ?

Re: Aggregate condition

sunny_talwar — Tue, 05 Nov 2019 15:10:49 GMT

May be this

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

Re: Aggregate condition

sunny_talwar — Tue, 05 Nov 2019 15:11:43 GMT

or even this

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

Re: Aggregate condition

geogou1973 — Tue, 05 Nov 2019 15:15:42 GMT

It works.

Thank you.