Difficult function

[#1]

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

[sunny_talwar]

May be an expression like this:

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

[marcus_sommer]

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

[#4]

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

[#5]

Thx for ur help Marcus,

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

-Eric

[marcus_sommer]

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

- Marcus

[#7]

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