Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
geogou1973
Contributor

Aggregate condition

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

Labels (1)
1 Solution

Accepted Solutions
Highlighted

Re: Aggregate condition

May be this

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

View solution in original post

9 Replies
Highlighted
Partner
Partner

Re: Aggregate condition

Have you tried to use sum outside that expression?

 

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

Highlighted
geogou1973
Contributor

Re: Aggregate condition

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

Highlighted
Partner
Partner

Re: Aggregate condition

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

Highlighted

Re: Aggregate condition

Try this

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

Re: Aggregate condition

how can i convert the folowind to set analysis expression

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

Highlighted
geogou1973
Contributor

Re: Aggregate condition

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 ?

Highlighted

Re: Aggregate condition

May be this

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

View solution in original post

Highlighted

Re: Aggregate condition

or even this

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

Re: Aggregate condition

It works.

Thank you.