Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
carlcimino
Creator II
Creator II

Weekly Visit Count by Cardholder and Location

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.

carlcimino_0-1657760238679.png

Any guidance or suggestions would be much appreciated!

Thanks.

 

Labels (2)
4 Replies
vinieme12
Champion III
Champion III

Refer the solution I posted here

 

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

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
carlcimino
Creator II
Creator II
Author

@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.

carlcimino_0-1657806286000.png

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

carlcimino_1-1657806345220.png

 

vinieme12
Champion III
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
carlcimino
Creator II
Creator II
Author

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.  

carlcimino_0-1658414786098.png

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