topic Re: count of MAX record for dynamically changing the date variables in QlikView

count of MAX record for dynamically changing the date variables

sai_yashwan — Wed, 23 Sep 2020 17:42:38 GMT

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.

Re: count of MAX record for dynamically changing the date variables

edwin — Thu, 24 Sep 2020 19:23:04 GMT

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

Re: count of MAX record for dynamically changing the date variables

sai_yashwan — Fri, 25 Sep 2020 00:33:59 GMT

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?

Re: count of MAX record for dynamically changing the date variables

Brett_Bleess — Thu, 15 Oct 2020 20:46:08 GMT

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