Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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.

Eric

6 Replies
MVP
MVP

Re: Difficult function

May be an expression like this:

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


Capture.PNG

Re: Difficult function

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(aggr(

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

     product))

- Marcus

Not applicable

Re: Difficult function

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

Re: Difficult function

Thx for ur help Marcus,

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

-Eric

Re: Difficult function

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

- Marcus

Not applicable

Re: Difficult function

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.

Thanks

-Eric