topic Re: Date set analysis showing incorrect count in QlikView

Date set analysis showing incorrect count

anothergreg — Wed, 25 Nov 2020 16:16:04 GMT

! !Qlikview is giving results that I'm not expecting. I'm assuming it's due to my syntax, but I've done a bunch to try and manipulate it and it's not cooperating.

I have an EVAL_DT field coming through as DDMMMYYYY as default. I have a variable vMaxEval defined as =max(EVAL_DT). I have another variable vMaxED defined in the load script as max(EVAL_DT) giving a value of 42916.

My syntax is supposed to show "Claim count for Jun 2017 Eval Date: 49,157" in a text box.

='Claim count for '&capitalize(date(max({1}EVAL_DT),'MMM YYYY'))&' Eval Date:

'&num(count({<[EVAL_DT]={"=$(vMaxED)"}>}distinct CLM_NBR),'#,##0')

The table attached shows the values in this order:

Dimension:

EVAL_DT

Expressions:

=Date($(vMaxED),'DDMMMYYYY')

=$(vMaxED)

=num(count({<[EVAL_DT]={"=Date($(vMaxEval),'DDMMMYYYY')"}>}CLM_NBR),'###0')

=count(CLM_NBR)

=count(if(Aggr(NODISTINCT Max(EVAL_DT),CLM_NBR) = EVAL_DT, CLM_NBR))

My counts always show up in the text box with 88,351 rather than the 49,157 as expected. I'm not sure where to go from here and I would appreciate if someone could scrutinize my syntax. For detail, I've already tried changing the format of the EVAL_DT to any version of a MDY that exists as well as bringing it in as a whole number with no commas to no avail.

Re: Date set analysis showing incorrect count

Anil_Babu_Samineni — Wed, 27 Sep 2017 13:54:04 GMT

It's tough to answer without sample. I assume, Date format issue

Re: Date set analysis showing incorrect count

sunny_talwar — Wed, 27 Sep 2017 13:58:08 GMT

Can you try this

=Num(Count({<[EVAL_DT]={"$(=Date($(vMaxEval),'DDMMMYYYY'))"}>}CLM_NBR),'###0')

Re: Date set analysis showing incorrect count

oscar_ortiz — Wed, 27 Sep 2017 14:01:19 GMT

Hard to answer without sample data.

Could you disable your first two expressions and see what results you get?

I do see that for the date of June 30th 2017 you are getting the value you are looking for.

Thanks

Oscar

Re: Date set analysis showing incorrect count

psankepalli — Wed, 27 Sep 2017 14:26:29 GMT

It would be really helpful to get the answer if you share some sample application with data.

Cheers

Re: Date set analysis showing incorrect count

anothergreg — Wed, 27 Sep 2017 15:07:52 GMT

All months in that column are 0 until 30JUN2017 which has 49157 when I use that syntax.

Re: Date set analysis showing incorrect count

sunny_talwar — Wed, 27 Sep 2017 15:11:55 GMT

Isn't that what you wanted? I am confused as to what the expected output needs to be?

Re: Date set analysis showing incorrect count

anothergreg — Wed, 27 Sep 2017 15:15:05 GMT

It should be 49157 for all months. Set analysis should give me the result for the month I'm looking for regardless of dimension. Otherwise it would just be a simple if(month=monthintended, sum(X)).

Re: Date set analysis showing incorrect count

sunny_talwar — Wed, 27 Sep 2017 15:18:02 GMT

Try this

=Num(Count(TOTAL {<[EVAL_DT]={"$(=Date($(vMaxEval),'DDMMMYYYY'))"}>}CLM_NBR),'###0')

Re: Date set analysis showing incorrect count

anothergreg — Wed, 27 Sep 2017 15:22:00 GMT

Added sample

Re: Date set analysis showing incorrect count

anothergreg — Wed, 27 Sep 2017 15:22:14 GMT

Added sample

Re: Date set analysis showing incorrect count

anothergreg — Wed, 27 Sep 2017 15:22:20 GMT

Added sample

Re: Date set analysis showing incorrect count

sunny_talwar — Wed, 27 Sep 2017 15:24:55 GMT

Check this

=Num(Count(TOTAL {<[EVAL_DT]={"$(=Date($(vMaxEval),'DDMMMYYYY'))"}>}CLM_NBR),'###0')

Re: Date set analysis showing incorrect count

anothergreg — Wed, 27 Sep 2017 15:27:45 GMT

This looks like it works at first, but I tried using this and included the full set {1} and my filters are changing the value. It should remain constant regardless of filters - it's an overall contents page and it should be a constant value always.

Re: Date set analysis showing incorrect count

sunny_talwar — Wed, 27 Sep 2017 15:30:19 GMT

Try this

=Num(Count(TOTAL {1<[EVAL_DT]={"$(=Date($(vMaxEval),'DDMMMYYYY'))"}>}CLM_NBR),'###0')

Also, update your variable vMaxEval to this

=max({1} EVAL_DT)

Re: Date set analysis showing incorrect count

anothergreg — Wed, 27 Sep 2017 15:36:07 GMT

THANK YOU!