topic Re: how do i put this needed result in a textbox value in QlikView

how do i put this needed result in a textbox value

amien — Sat, 16 Jul 2011 21:30:15 GMT

i have this pivot;

dimension: material

expression : MAX(quantity),SUM(safetystock),[quality]-[safetystock]

i want to put the count on the results where quality-safetystock < 0 into a textbox.

So i want in a textbox how many materials are below safetystock.

how does this expression look like?

Re: how do i put this needed result in a textbox value

Miguel_Angel_Baeyens — Sat, 16 Jul 2011 22:18:57 GMT

Hello Amien,

Not sure if I understood you right (I'm assuming the labels are respective to the Max() and Sum() function), but if that returns more than one line, use better a straight table without dimensions with something like the following:

Count({< ID = {"=Max(quantity)", "=Sum(quality - safetystock) < 0"} >} ID)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: how do i put this needed result in a textbox value

amien — Sat, 16 Jul 2011 22:29:00 GMT

Thanks for your reply..

Yes those are the labels of max() and sum()

only your formula doesn't give me the end result that i need.

If i have this in an expression:

IF(MAX(Quantity) - SUM(SafetyStock) < 0,1,0)

i need the sum of that (all the '1') into a single textbox

Re: how do i put this needed result in a textbox value

Miguel_Angel_Baeyens — Sat, 16 Jul 2011 22:41:14 GMT

Hi,

Then try something like

Count({< ID = {"=Max(Quantity) - Sum(SafetyStock) > 0"} >} ID)

I'm using the "ID" as the field that identifies those Quantity and SafetyStock (Items maybe)?

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: how do i put this needed result in a textbox value

amien — Sat, 16 Jul 2011 22:52:46 GMT

same result

btw ..

not items, but plants.

each plant has different quantities on each material (max)

i dont really need to see the plant

and each material has a needed safety stock which is only one per material (sum)

so the sum is not really needed cause all materials only have one safety stock

how do i put this needed result in a textbox value

amien — Sun, 17 Jul 2011 15:26:33 GMT

Dear Miguel,

i have added an example .. in my textbox/button .. i need the value '2' .. because 2 materials are below safetystock

how do i put this needed result in a textbox value

amien — Mon, 18 Jul 2011 09:25:28 GMT

SUM(IF(AGGR((MAX([Quantity]) - SUM([SafetyStock])),[MaterialID]) < 0,1))