topic Re: continues in set analysis in QlikView

continues in set analysis

soniasweety — Sat, 16 Nov 2024 17:50:16 GMT

Hi All,

@tresB @MayilVahanan @Taoufiq_Zarra

i wan to count the user id where L1 exams continues 3times failed only ..

here is my expression

count({<level={1}},avgscore ={"<60"}>}User_id) here failed criteria is ( <60% is failed ) I have field Avg_score.

I wan to include 3times continues failed Leval1 failed candidates only.

Re: continues in set analysis

Taoufiq_Zarra — Thu, 12 Nov 2020 09:28:28 GMT

@soniasweety can you share a sample data ? and the expected also from this sample ?

Re: continues in set analysis

soniasweety — Thu, 12 Nov 2020 09:47:50 GMT

@Taoufiq_Zarra attached sample. Need result in setanalysis.

Re: continues in set analysis

Taoufiq_Zarra — Thu, 12 Nov 2020 10:08:05 GMT

@soniasweety May be this :

=count({<Level={'1'},user={"=count({<Avgscore={'<60'}>}Level)>2"}>} distinct user)

output:

Re: continues in set analysis

MayilVahanan — Thu, 12 Nov 2020 10:20:03 GMT

Hi @soniasweety

Try like below. Might be, you can include additional condition in if statement.

LOAD user,
Level,
Status,
Avgscore,
If(Status = 'Fail' and Status = Peek('Status') and user = Peek(user), 1+Peek('Cont'), 1) as Cont
FROM
[score.xlsx]
(ooxml, embedded labels, table is Sheet1);

Exp: count({<level={1},avgscore ={"<60"}, Cont={">=3"}>}user)

Re: continues in set analysis

soniasweety — Thu, 12 Nov 2020 10:49:00 GMT

@Taoufiq_Zarra same expression I tried its showing 0 as result .. can you attach your sample.

Re: continues in set analysis

Taoufiq_Zarra — Thu, 12 Nov 2020 10:50:38 GMT

@soniasweety attached

Re: continues in set analysis

soniasweety — Thu, 12 Nov 2020 10:55:51 GMT

@Taoufiq_Zarra

Still strange … its showing zero to me im my qvw 😞 attached my sample . am I missing something

Re: continues in set analysis

Taoufiq_Zarra — Thu, 12 Nov 2020 11:01:21 GMT

@soniasweety for me its 4 (your sample )

Re: continues in set analysis

soniasweety — Thu, 12 Nov 2020 11:06:26 GMT

Im not sure.. any other alternate solution ? for me its showing zero 😞

Re: continues in set analysis

soniasweety — Thu, 12 Nov 2020 11:27:08 GMT

Thanks . but without modifying script cant we do in front end only?

Re: continues in set analysis

tresB — Thu, 12 Nov 2020 11:38:52 GMT

@soniasweety , @Taoufiq_Zarra Try:

=count({<Level={'1'},user={"=count({<Avgscore={[<60]}>}Level)>2"}>} distinct user)

Note: quotation has got modified meaning in latest versions of qv. And with this expression both of you would see the same result, unlike in the other case (expression with single quotes). 🙂

Re: continues in set analysis

soniasweety — Thu, 12 Nov 2020 11:45:14 GMT

Good to know 🙂 thanks its working fine now.

Re: continues in set analysis

soniasweety — Tue, 17 Nov 2020 14:16:53 GMT

Hi @tresB @MayilVahanan

one small issue im facing .. I want to exclude null values in this set expression.. how can I exclude nulls?

=count({<Level={'1'},user={"=count({<Avgscore={[<60]}>}Level)>2"}>} distinct user)

Re: continues in set analysis

MayilVahanan — Wed, 18 Nov 2020 02:25:29 GMT

Hi @soniasweety

Exclude nulls based on which field? , please find the common syntax to exclude nulls in set analysis

{<YourFieldName= {"=Len(Trim(YourFieldName)) > 0"}

{<YourFieldName= {"*"}>}

Re: continues in set analysis

soniasweety — Wed, 18 Nov 2020 05:06:34 GMT

Hi ,

Thank you. I tried this working fine.

Field-={""}