Qlik Sense Master item help

[maarcus_smith]

Hi,

im trying to get my master item measure correct but im having trouble coming up with the correct formula. 

my current formula gets the sum of my row counts / sum of my failure counts:

sum([Failure Mode Count]) / count(distinct[Record Count])

however the issue here is that if my row count is 1 but within that row i have multiple failures, the % will be wrong.

what i need is regardless of how many failures on a row, it will only show as 1 fail on a row max. or zero if no fails obviously

im puling in numerous csv files and im analyzing each row/value.

so my row count = total number of rows on all my csv files (based on selection)

failure count = total number of a specific value while searching all value .

is there a way to do the above? where if a row has a failure count, the count is 1, regardless of how many fails in that row?

many thanks

[vikasmahajan]

try this use TOTAL :

sum([Failure Mode Count]) / sum( Total [Record Count])

Thanks

Vikas

[maarcus_smith]

hi @vikasmahajan , the issue i think is with the failure mode count, i dont want more than one failure on each record count,

getting a total of my record count doesnt do the trick for me.

e.g. if my record count is 10, that means i have 10 rows of data, each row can contain up to 50 values.

if row 5 has a value that is a failure, that failure mode for that row = 1,

if row 6 has 2 values that is a fail, that failure mode is still 1. 

then ideally i can do a total sum/count of my records and my fails to get a % of number of rows with 1 or more fails

thanks

[vikasmahajan]

Have you try this ?

sum( TOTAL [Failure Mode Count]) / sum( [Record Count])

Vikas

[maarcus_smith]

@vikasmahajan  yes the total function does not work.

using that function i get a value of 2 for my failure count when i have it filtered down to a specific row, whereas i want a value of 1, even tho there are 2 failures on that row:

[maarcus_smith]

for reference, i also have a compound unique key generated for each row if that helps with the calculation.