Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anothergreg
Contributor III
Contributor III

Date set analysis showing incorrect count

! !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.

15 Replies
anothergreg
Contributor III
Contributor III
Author

Added sample

anothergreg
Contributor III
Contributor III
Author

Added sample

sunny_talwar

Check this

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

Capture.PNG

anothergreg
Contributor III
Contributor III
Author

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.

sunny_talwar

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)

anothergreg
Contributor III
Contributor III
Author

THANK YOU!