Weekly Visit Count by Cardholder and Location

[carlcimino]

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.

[vinieme12]

Refer the solution I posted here

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

[carlcimino]

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

[vinieme12]

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)

[carlcimino]

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