Skip to main content
The way to achieve your own success is the willingness to help somebody else. Go for it!
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Difficult function

Hi community,

once again i have a question.

The goal is a NOSBOS statement.

So that means Not on shelf but on storage.So when i have

product size colour       amount      location    

a          40     green            1           store

a          44     red               1           store

b          40     brown           1           store

b          20     yellow          1           store

and (same table just splitted for better overview for u)

product size colour            amount       location

a          40     green              1               storage

a          44     red                 1               storage

b          40     brown             1               storage

b          20     yellow             1               storage

b          23     blue             1                  storage

c          12     cyan            1                  storage

We see that

a 40 green 1 (is both in store and storage)

a 44  red   1  (is both in store and storage)

same with b 40 and 20

but we have

b 23 blue 1                in storage

and c 12 cyan 1           in storage

But ur goal is to have all we have in storage at the store so people can buy it.

So a this point we have 4 of 6 products at the store and a rate of 66,66--%

We would have 100% if 6/6 products where at the store.

A lot of words im SO SORRY , but how do i get this rate. I tried but i failed hard.

Hope u can help me, thanks in advance.


6 Replies

May be an expression like this:

=Num(Count({<location = {'store'}>}product)/Count({<location = {'storage'}>}product), '##.##%')


MVP & Luminary
MVP & Luminary

I'm not sure if I had understood your data-structures right but something like this may work:

sum({< location = {'store'}>} amount) / sum({< location = {'storage'}>} amount)

and maybe aggregated to a higher level with:


     sum({< location = {'store'}>} amount) / sum({< location = {'storage'}>} amount),


- Marcus

Not applicable

Hi Sunny,

thanks for ur help, i think ur solution goes into right direction.

But my question is where does the different color and size fit in.

or is that auto?(like in my other expressions)

i need an answer to this because that makes me wondering about the right expression every time .Sorry

Not applicable

Thx for ur help Marcus,

but for ur solution i have the same question with the different size and color.


MVP & Luminary
MVP & Luminary

I think a small example with a few inline-data and how the results should be displayed would be helpful.

- Marcus

Not applicable

Hi Marcus,

i play around with Sunny's Solution and atm its works but i must test a bit more.

But thx for now.

I just get kind of confused every time because i dont think that this(color and size) will be realized in the funktion but

Qlik Sense just use it even when its not in the expression.Im just simply not used to this.