Multiple Criteria in Sum Expression

[ZoeM]

Hello Community,

Happy Friday! I am having trouble with a sum expression in a chart. I have a table of data and I want to count how many tries each component has had during testing.

A component is tested at Phase 2 first and it can either Pass or Fail. If it Passes, we will count it as either Pass at 1.

However, I am trying to count how many had 2 tries and more than 2 tries. If a component is tested at Phase 2 and has a Pass of 2 Tries, then count or if it is tested at Phase 2 and has a Fail of 1 Try and then tested at Phase 3 and has a Pass of 1 Try then the sum of those two is to be considered as a Try of 2 and should be counted.

I have attached the qvw file, please any help with this will be truly appreciated. 

[sunny_talwar]

Based on the sample file provided, what do you expect the final count to be?

[ZoeM]

1 Try Pass = Dodge Charger 1124

2 Tries = Dodge Charger 1124, 1123 (Phase 2 Fail with 1 Try and Phase 3 Pass with 1 Try)

> 2 Tries = ...

I am thinking that once the 1123 for Dodge Charger is figured out then the > 2 Tries should be follow suit.

Does this help?

[ZoeM]

Hey Sunny,

Maybe I over thought it, but will the attached work?

I think its the result I was looking for...

[sunny_talwar]

Number of tries for Dodge Charger 1123 and 1124 are not 1 in both phases

If we stick with 1123, first condition looks good that Phase 2 Fail with 1 try, but Phase 2 Pass with 2 tries... isn't it?

[sunny_talwar]

Nice 🙂

[ZoeM]

so how can I wrap that formula in a set analysis so that for 2 tries  and greater than 2 tries I can count how many line items are associated with it?

I have seen the below thread but was not able to get it work:

https://community.qlik.com/t5/New-to-QlikView/count-sum-in-set-analysis/td-p/493720

[sunny_talwar]

You can create a key like this in the script

LOAD Industry, 
     Make, 
     Item, 
     State, 
     Components, 
     Action, 
     [# Tries],
     Hash128(Industry&'|'&Make&'|'&Item&'|'&Components) as Key
FROM
[DV test file.xlsx]
(ooxml, embedded labels, table is Sheet1);

and then use this expression

Sum({<Key = {"=Sum([# Tries]) > 1 and Sum([# Tries]) < 3"}>}[# Tries])