topic Re: Weekly Visit Count by Cardholder and Location in App Development

Weekly Visit Count by Cardholder and Location

carlcimino — Thu, 14 Jul 2022 01:00:04 GMT

Hello, I have some sample data and am having difficulty rolling up. The data has Location, Cardholder, Date of Visit, WeekRefDate (this is datestart("Date of Visit"), WeekYear. What I want to know is by Location how many Cardholders have visited the office at least once, twice, three+ times.

I can get the total cardholder visits using

sum(aggr(Count({<WeekYear={'$(vMaxWeekSelect)'}>} distinct [Cardholder]),WeekRefDate,[Location]))

but I can't seem to figure out how to group it based on the Cardholder visiting 1 or more, 2 or more or 3+ times in that week.

Any guidance or suggestions would be much appreciated!

Thanks.

Re: Weekly Visit Count by Cardholder and Location

vinieme12 — Thu, 14 Jul 2022 07:06:02 GMT

Refer the solution I posted here

https://community.qlik.com/t5/App-Development/How-to-find-No-of-associates-visited-office-in-a-week/td-p/1955516

Re: Weekly Visit Count by Cardholder and Location

carlcimino — Thu, 14 Jul 2022 14:07:32 GMT

@vinieme12 thanks for the suggestion. that is really close to what I am looking for. The difference is I want the left hand column to be the office that the visit was made in not the week. So for a given week selected I want to see the by office performance of # of visits.

WeekRefDate serves the same purpose as IsoWeek in your aggr I believe. I tried this based on your post but I only get Once. Any ideas? Really appreciate the help feel like we're getting close!

=aggr(if(Count({<WeekYear={'$(vMaxWeekSelect)'}>} distinct [Cardholder])=1,'Once',
if(Count({<WeekYear={'$(vMaxWeekSelect)'}>} distinct [Cardholder])=2,'Twice',
if(Count({<WeekYear={'$(vMaxWeekSelect)'}>} distinct [Cardholder])=3,'Thrice',
if(Count({<WeekYear={'$(vMaxWeekSelect)'}>} distinct [Cardholder])=4,'Four Times',
if(Count({<WeekYear={'$(vMaxWeekSelect)'}>} distinct [Cardholder])=5,'Every Day'))))),WeekRefDate,[Cardholder])

Re: Weekly Visit Count by Cardholder and Location

vinieme12 — Fri, 15 Jul 2022 07:57:25 GMT

As below

=aggr(if(count(Cardholder)=1,'Once',
if(count(Cardholder)=2,'Twice',
if(count(Cardholder)=3,'Thrice',
if(count(Cardholder)=4,'Four Times',
if(count(Cardholder)=5,'Five Times',
if(count(Cardholder)=6,'6 Times',
if(count(Cardholder)=7,'Everyday'))))))),Location,WeekRefDate,Cardholder)

Re: Weekly Visit Count by Cardholder and Location

carlcimino — Thu, 21 Jul 2022 14:50:24 GMT

This helps getting closer... I can see who is in what day, how many days in a week. But the counts are not aggregating properly. They all should fall into 1x or more, 4 of them should be 2x or more, 3 of them should be 3x or more.

aggr dimension expression:

=aggr(if($(v_Full_Week_Swipes)>=3,'3x or More',
if($(v_Full_Week_Swipes)>=2,'2x or More',
if($(v_Full_Week_Swipes)>=1,'1x or More'))),[Location Description],WeekRefDate,[Badge Number])

v_Full_Week_Swipes = the sum of the following.

Count({<WeekYear={'$(vMaxWeekSelect)'},WeekDay={'Mon'}>} distinct [Badge Number])

Count({<WeekYear={'$(vMaxWeekSelect)'},WeekDay={'Tue'}>} distinct [Badge Number])

Count({<WeekYear={'$(vMaxWeekSelect)'},WeekDay={'Wed'}>} distinct [Badge Number])

Count({<WeekYear={'$(vMaxWeekSelect)'},WeekDay={'Thu'}>} distinct [Badge Number])

Count({<WeekYear={'$(vMaxWeekSelect)'},WeekDay={'Fri'}>} distinct [Badge Number])

measure expression:

Count({<WeekYear={'$(vMaxWeekSelect)'},WeekDay={'Mon','Tue','Wed','Thu','Fri'}>} distinct [Badge Number])