Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
! !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.
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)
It's tough to answer without sample. I assume, Date format issue
Can you try this
=Num(Count({<[EVAL_DT]={"$(=Date($(vMaxEval),'DDMMMYYYY'))"}>}CLM_NBR),'###0')
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
It would be really helpful to get the answer if you share some sample application with data.
Cheers
KR
All months in that column are 0 until 30JUN2017 which has 49157 when I use that syntax.
Isn't that what you wanted? I am confused as to what the expected output needs to be?
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)).
Try this
=Num(Count(TOTAL {<[EVAL_DT]={"$(=Date($(vMaxEval),'DDMMMYYYY'))"}>}CLM_NBR),'###0')
Added sample