Count expression incorrect

[jim_chan]

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

[jim_chan]

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

[sunny_talwar]

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

[swuehl]

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

[jim_chan]

Bro,

I think this is the one.

I have scramble it.

Rgds

Jim

[jim_chan]

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)

[swuehl]

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 😉

[sunny_talwar]

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

[jim_chan]

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

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

[sunny_talwar]

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

[jim_chan]

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