topic Re: Count expression incorrect in QlikView

Count expression incorrect

jim_chan — Wed, 19 Oct 2016 00:14:04 GMT

HI guys,

I;ve managed to get a list of new records based on script. but i dunno what is missing.

=Count(DISTINCT {<risk_item_id = e({<risk_item_id = {"=Count({<batch_year = {'<$(=Max(batch_year))'}>} risk_item_id) > 0"}>})>} risk_item_id)

Based on the above script - i manage to get the correct new records - which is 67.

But when select filter risk1 'low and risk2 'low' then i got the wrong figure, 104. is wrong.

The result should be 49, after i have filtered with risk1 and risk 2.

rgds

Jim

Re: Count expression incorrect

sunny_talwar — Wed, 19 Oct 2016 02:50:33 GMT

You need to start providing sample Jim. Here look at these threads which will tell you why we need a sample and will help guide you in preparing a sample and loading it here:

QlikCommunity Tip: How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Help us help you better and faster.

Re: Count expression incorrect

jim_chan — Wed, 19 Oct 2016 03:11:41 GMT

bro, is not that i dowan to provide. i can reduce i can scramble. but how to hide the data model? i can upload the qvw here, but i need to hide the datamodel.

Re: Count expression incorrect

sunny_talwar — Wed, 19 Oct 2016 03:14:02 GMT

I have heard about confidential data, but never heard about confidential data model

Re: Count expression incorrect

jim_chan — Wed, 19 Oct 2016 03:17:14 GMT

ookokkkkkkk....

this is the sample bro. it is using your expression.

rgds

jim

Re: Count expression incorrect

sunny_talwar — Wed, 19 Oct 2016 03:22:38 GMT

What are you expecting to see with and without selections?

Re: Count expression incorrect

sunny_talwar — Wed, 19 Oct 2016 03:31:36 GMT

See if this is what you want

=Count(DISTINCT {<risk_item_id = e({<risk_item_id = {"=Count({1<batch_year = {'<$(=Max(batch_year))'}>} risk_item_id) > 0"}>})>} risk_item_id)

No selection

NOFEYT selected

APOMRPY Selected

CQBNSGZQ

Re: Count expression incorrect

sunny_talwar — Wed, 19 Oct 2016 03:32:22 GMT

If the requirement is met, you can go ahead and remove your attachment

Re: Count expression incorrect

jim_chan — Wed, 19 Oct 2016 03:46:22 GMT

how to remove it bro ?

Re: Count expression incorrect

sunny_talwar — Wed, 19 Oct 2016 03:49:00 GMT

‌You can go into Editing your response and from there you can remove your attachment by clicking on the x next to your attachment file.

Re: Count expression incorrect

jim_chan — Sat, 21 Jan 2017 12:09:43 GMT

Dear Sunny,

Based on this script.

=Count(DISTINCT {<risk_item_id = e({<risk_item_id = {"=Count({1<batch_year = {'<$(=Max(batch_year))'}>} risk_item_id) > 0"}>})>} risk_item_id)

What to change if i want compare it instead of by year , compare it by quarterly. ?

I have the field called - batch_type : with data 1ST HALF , 2ND HALF. can max(batch_type) , but is not a number.

Rgds

Jim

Re: Count expression incorrect

sunny_talwar — Sat, 21 Jan 2017 14:56:08 GMT

Its been three months now... It would be nice if you can share a sample again

Re: Count expression incorrect

swuehl — Sat, 21 Jan 2017 15:06:43 GMT

Max() won't work on text values, but maybe MaxString() function can be helpful here?

Re: Count expression incorrect

jim_chan — Sat, 21 Jan 2017 15:17:23 GMT

Bro,

I think this is the one.

I have scramble it.

Rgds

Jim

Re: Count expression incorrect

jim_chan — Sat, 21 Jan 2017 15:19:03 GMT

Hi Stefan.

do you mean just need to change the batch year to batch_type will do..?? like this

=Count(DISTINCT {<risk_item_id = e({<item_id = {"=Count({1<batch_type = {'<$(=Maxstring(batch_type))'}>} risk_item_id) > 0"}>})>} item_id)

Re: Count expression incorrect

swuehl — Sat, 21 Jan 2017 15:29:02 GMT

No idea if this is enough without knowing your data model, data and expression context.

Just saying that there are a lot other aggregation functions you can use 😉

Re: Count expression incorrect

sunny_talwar — Sat, 21 Jan 2017 15:33:29 GMT

I am getting 3967... is that the right number?

Re: Count expression incorrect

jim_chan — Sat, 21 Jan 2017 15:42:07 GMT

Can the expression be modified to calculate by quarterly, instead of max year?

Can I change the max year to maxstring(batch_type)?

Re: Count expression incorrect

sunny_talwar — Sat, 21 Jan 2017 15:44:17 GMT

That is what I tried doing... is 3967 the number you are hoping to get?

Re: Count expression incorrect

jim_chan — Fri, 03 Feb 2017 19:03:27 GMT

Dear Sunny,

I have re-create a new sample.qvw file as per attached.

in this exmaple my records data for year 2015 and 2016 is loaded into 1 table.

There are few scenario i wanted to achieve. But i am not getting it from this expression:

Count(DISTINCT {<record_id = e({<record_id = {"=Count({<year = {'<$(=Max(year))'}>} record_id) > 0"}>})>} record_id)

By using the above expression for the New Risk - it gives me 3 new risks from 2015-2016. But that's not what i need now.

If the user select year 2016, 2nd Half, version 4 - it should display 1 Risk only, when it compares to year 2016 1st half version 3

If the user select year 2016, 1st half, version 3 - it should display 2 Risks only, when it compares to year 2015 2nd half version 2

Those expected result i have already written in qvw file.

- Expected result for NEW RISK.

If i click on year 2016 2nd Half version:4

New Risk found = 1, record id = 98071 compare to 2016 1st half version 3

- Expected result for DELETED RISK.

If i click on year 2016 2nd Half version:4

Deleted Risk = 2 , record id: 56748 & 98596

Please help Sunny.

Rgds

Jim