Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a requirement to display max records between the date range (changes dynamically)
Attache the data
This expression gives everything out there. however, I need to display the latest record within the selected date range
forex, if the user selects the date range '20181019' to '20190415', I need Benefitrnk = 4 only be counted.
=Count(distinct if( Case='BV' and Case_Status='Completed' and Date ='CaseCreatedDate' and (CalendarDate>='20181023' and CalendarDate<='20190305' ) , Profile_ID))
Any insight would help me. Many thanks for considering my request.
Sai.
a bit confusing looking at the data, because for 20190415, Benefitrnk is 3 not 4.
if you want to count only the data set where date is max of the selected range, you can create a variable
vMaxDate=max(Date)
=Count(distinct if( Case='BV' and Case_Status='Completed' and Date ='CaseCreatedDate' and (CalendarDate='$(vMaxDate)' ) , Profile_ID))
but i suggest using set analysis:
=Count(distinct {<Case={'BV'},Case_Status={'Completed'},Date ={'CaseCreatedDate'},CalendarDate={'$(vMaxDate)'}>} Profile_ID))
Thanks, Edwin, Appreciate the response!
But the requirement is that dates are dynamically changing based on the user selection calendar icons for start and from dates.. so I need the max value between a start and end dates...CalendarDate>='$(vStartDate)' and CalendarDate<='$(vEndDate)' ..so I tried to modify the logic to pull the max count but its not working ..can you help?
About the best I have is the following Design Blog area base link, search around there and see if you can find any posts that may help you with things. I generally start with a single word, see what pops up, and then rework or narrow down from there until I find something that looks interesting. What you may have going on with variables is a potential timing issue, just FYI that is causing an issue in that the expression is calculating before the variables are updated etc. One potential snag I can see here depending upon how things are setup to populate. This will kick things back up too, so maybe someone else will have a look too.
https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog
Regards,
Brett