only Function()

[Samanehsorournejad]

SyntaxHi Every body,

I have a Column named "ok"  it could be 0 and 1 and it shows me my Status I have some other parameters as my filter they could be 0 and 1 such as

[RZV:QlikSense Abbruch] ,[RZV:Qlikview Abbruch] ,[RZV:Sonstiges Abbruch]

or 

[DWH:QlikSense Abbruch] ,[DWH:Qlikview Abbruch] ,[DWH:Sonstiges Abbruch]

and so on

and I want to calculate how much of my "ok"=0 caused by DWH

So I had try this measure but I could not understand what is my fault:

actually it does not give me any error but the answer is not correct.

(count({$<[RZV:QlikSense Abbruch]={"$(=Only({$<[RZV:QlikSense Abbruch]={'1'}>} ok))"},ok={'0'}>} ok)
+
count({$<[RZV:Qlikview Abbruch]={"$(=Only({$<[RZV:Qlikview Abbruch]={'1'}>} ok))"},ok={'0'}>} ok)

+
count({$<[RZV:Sonstiges Abbruch]={"$(=Only({$<[RZV:Sonstiges Abbruch]={'1'}>} ok))"},ok={'0'}>} ok)

+
count({$<[RZV:UC4 Abbruch]={"$(=Only({$<[RZV:UC4 Abbruch]={'1'}>} ok))"},ok={'0'}>} ok)

+

count({$<[RZV:Spiegel-Abbruch Fehler]={"$(=Only({$<[RZV:Spiegel-Abbruch Fehler]={'1'}>} ok))"},ok={'0'}>} ok)
)
/

count(total {<ok={'0'}>} ok)

I think the problem is  this part {"$(=Only({$<[xxx]={'1'}>} ok))"} in every count,

because in the button of the page it shows me like this:

count({$<[XX]={"0"} other wise I had write in my formula 1 but I could not understand why show me 0.

I had attached the syntax message.

please Help me 😞

[Samanehsorournejad]

[vinieme12]

You don't need only() at all , only() returns a single values

count({$<[RZV:QlikSense Abbruch]={'1'},ok={'0'}>} ok)

+

count({$<[RZV:Qlikview Abbruch]={'1'},ok={'0'}>} ok)

+

count({$<[RZV:Sonstiges Abbruch]={'1'},ok={'0'}>} ok)

+

count({$<[RZV:UC4 Abbruch]={'1'},ok={'0'}>} ok)

+

count({$<[RZV:Spiegel-Abbruch Fehler]={'1'},ok={'0'}>} ok)

)

/

count(total {<ok={'0'}>} ok)

[Samanehsorournejad]

Hi Dear Vineeth ,

thanks for your answer let me better explain. I have two department DWH and RZV, I have ok column that show me my Status.  I have two measures, one of them give me how much percentage of the ok=0 cause by DWH and another shows me how much cause by RZV .

I had tried these two measures like this that you told me but the Problem is that the sum of this two measures exceed more than 100% and this is my Problem 😞

Measure 1:

(Count({$<[RZV:QlikSense Abbruch]={'1'},ok={'0'}>}ok)
+
Count({$<[RZV:Qlikview Abbruch]={'1'},ok={'0'}>}ok)
+
Count({$<[RZV:Sonstiges Abbruch]={'1'},ok={'0'}>}ok)
+
Count({$<[RZV:UC4 Abbruch]={'1'},ok={'0'}>}ok)
+
Count({$<[RZV:Spiegel-Abbruch Fehler]={'1'},ok={'0'}>}ok))
/
count( total {<ok={'0'}>}ok)

Measure 2:

(Count({$<[DWH:Pentaho Abbruch]={'1'},ok={'0'}>}ok)
+
Count({$<[DWH: Laufzeit-problem]={'1'},ok={'0'}>}ok)
+
Count({$<[DWH: QlikSense Inhaltlich]={'1'},ok={'0'}>}ok)
+
Count({$<[DWH:QlikSense techn. Integration]={'1'},ok={'0'}>}ok)
+
Count({$< [DWH:Qlikview Abbruch]={'1'},ok={'0'}>}ok)
+
Count({$< [DWH:Sonstiges Abbruch]={'1'},ok={'0'}>}ok)
+
Count({$<[DB:Sonstiges ]={'1'},ok={'0'}>}ok)
)
/
Count(total {$< ok={'0'}>}ok)

*just for info: there are cases that could both features equal 1. for example [DWH:Qlikview Abbruch]={'1'} and [RZV:Qlikview Abbruch]={'1'}

please help me 😞

[vinieme12 · Accepted solution]

you shouldn't be counting Ok field then, instead use a field that identifies each row uniquely

count({

<[RZV:QlikSense Abbruch]={'1'},ok={'0'}>+<[RZV:Qlikview Abbruch]={'1'},ok={'0'}>

+$<[RZV:Sonstiges Abbruch]={'1'},ok={'0'}>+<[RZV:UC4 Abbruch]={'1'},ok={'0'}>

+<[RZV:Spiegel-Abbruch Fehler]={'1'},ok={'0'}>}  distinct Keyfield)

/

count(distinct total {<ok={'0'}>} keyfield)

[Samanehsorournejad]

Hi Vineeth,

 we read from excel and write into  DB. I had create the  Auto increment key in the Target as Keyfield and thn write my Measure exactly like you:

Measure 1:

(count({

$<[RZV:QlikSense Abbruch]={'1'},ok={'0'}>+$<[RZV:Qlikview Abbruch]={'1'},ok={'0'}>

+$<[RZV:Sonstiges Abbruch]={'1'},ok={'0'}>+$<[RZV:UC4 Abbruch]={'1'},ok={'0'}>

+$<[RZV:Spiegel-Abbruch Fehler]={'1'},ok={'0'}>} distinct id))

/

(count(distinct total {<ok={'0'}>} id))

Measure 2:

(count({

$<[DWH: Laufzeit-problem]={'1'},ok={'0'}>+< [DWH: QlikSense Inhaltlich]={'1'},ok={'0'}>

+$< [DWH:Pentaho Abbruch]={'1'},ok={'0'}>+< [DWH:QlikSense techn. Integration]={'1'},ok={'0'}>

+$< [DWH:Qlikview Abbruch]={'1'},ok={'0'}>
+$<[DWH:Sonstiges Abbruch]={'1'},ok={'0'}>

}

distinct id))

/

(count(distinct total {<ok={'0'}>} id))

but again the same Problem the sum is more than 100%

what should I do now?Pls Help 😞

[vinieme12]

please post some sample data

[Samanehsorournejad]

Hi Vinteeth,

thanks for your Answer, I had understand what was the Problem:

Since I had some Record that both departments cause ok=0 then it is normal that the sum will be more than 100%

because I have the Data that participate in both Department 🙂