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.

1 Solution

Accepted Solutions
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)

View solution in original post

15 Replies
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

Can you try this

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

oscar_ortiz
Partner - Specialist
Partner - Specialist

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

psankepalli
Partner - Creator III
Partner - Creator III

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

Cheers

KR

anothergreg
Contributor III
Contributor III
Author

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

sunny_talwar

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

anothergreg
Contributor III
Contributor III
Author

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

sunny_talwar

Try this

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

anothergreg
Contributor III
Contributor III
Author

Added sample