Forum Discussion
Helper IGoing from usage to usage frequency over time
Anonymous, thank you so much for taking the time. I eneded up just doing it step by step.
1. I added 5 dimensions in my original table, one for each period with the period date
2. I created 5 group queries, each grouping on a different dimension
3. I merged the 5 queries and got one master table with bucets per period
Hi milpro011,
You can refer to below steps to achieve your requirement.
Table.
Add calculate column to calculate the frequency.
frequency =
var currWeekNum=WEEKNUM(MAX([Date]),1)
Var actionCount =
if(currWeekNum>1,
COUNTX(FILTER(ALL('Calculate Count'),
OR(WEEKNUM([Date],1)=WEEKNUM(EARLIER('Calculate Count'[Date]),1),WEEKNUM([Date],1)=WEEKNUM(EARLIER('Calculate Count'[Date]),1)-1)&&
AND(YEAR([Date])=YEAR(EARLIER([Date])),[#Actions]=EARLIER('Calculate Count'[#Actions]))),[Date])
,
COUNTX(FILTER(ALL('Calculate Count'),
OR(WEEKNUM([Date],1)=WEEKNUM(EARLIER('Calculate Count'[Date]),1)&&
YEAR([Date])=YEAR(EARLIER([Date])),
WEEKNUM([Date],1)=WEEKNUM(Date(YEAR(EARLIER('Calculate Count'[Date])),12,31),1)&&
YEAR([Date])=YEAR(EARLIER([Date]))-1)&&[#Actions]=EARLIER('Calculate Count'[#Actions])
),[Date])//condition to deal with year transfer
)
return
if(actionCount>=9,"daily",if(actionCount>=5,"weekly",if(actionCount>=1,"occasionally","Inactive")))
Create a summary table to show the detail information.
Summary of Week =
var temp= DISTINCT(SELECTCOLUMNS('Calculate Count',"WeekNumber",WEEKNUM([Date],1),"Current frequency",[#Actions]&": "&[frequency]))
return
SUMMARIZE(temp,[WeekNumber],"Detail",CONCATENATEX(FILTER(temp,[WeekNumber]=EARLIER([WeekNumber])),[Current frequency]&", "))
In addition, I find that the "inactive" actions seems not appear on above summary table, I will keep researching to solve this issue.
Regards,
Xiaoxin Sheng
Helper IAnonymous, thank you so much for putting together the potential solution. However, I do feel that I must have wrongly explained my actual problem. I'll give it another try.
1. #of actions / day is irrelevant info - my bad. As long as a user has at least 1 action / day - he was active on that day.
2. I am looking to find how many days were people active over a period of 14 days. So, if in 14 days someone was active 9+ days, then I consider that user to be a daily user, etc.
Now, what I want to create are weekly or monthly snapshots of my situation. It would be great to be able to view last 12 periods, whether it's months or weeks.
So, for every period, that has a cut-off date, for example, we take a cut-off date to be the end of a particular week/month:
1. Create a subset of data by disregarding any activity after the cut-off
2. Look back 14 days from that date
3. Create usage frequency buckets / per user for that "cut-off date"
For any period, whether it's taken from the end of the week or month, there can only be one "daily", one "weekly" ..etc. buckets with X, Y, Z users in it respectfully.
Finally, plot the usage frequency per cut-off date.
Any clues?
Thanks, m.
Hi milpro011,
It sounds like I have misunderstood your requirement, you want to calculate the frequency by user,right?
If this is a case, I have modify above formula to calculate the users(a bit modify).
Calculate column:
frequency =
var currWeekNum=WEEKNUM(MAX([Date]),1)
Var actionCount =
COUNTAX(FILTER(ALL('Calculate Count'),
AND([Date]>=DATEADD('Calculate Count'[Date],-14,DAY)&&[Date]<=EARLIER([Date]),[User]=EARLIER('Calculate Count'[User]))),[User])
return
if(actionCount>=9,"daily",if(actionCount>=5,"weekly",if(actionCount>=1,"occasionally","Inactive")))Summary of Week =
var temp =DISTINCT(SELECTCOLUMNS('Calculate Count',"WeekNumber",WEEKNUM([Date],1),"User",[User],"frequency",[frequency]))
var result=SELECTCOLUMNS(DISTINCT(SELECTCOLUMNS(temp,"Week",[WeekNumber],"User",[User],"frequency",
if(CONTAINS(FILTER(temp,[User]=EARLIER([User])&&[WeekNumber]=EARLIER([WeekNumber])),[frequency],"daily"),"daily",
if(CONTAINS(FILTER(temp,[User]=EARLIER([User])&&[WeekNumber]=EARLIER([WeekNumber])),[frequency],"weekly"),"weekly",
if(CONTAINS(FILTER(temp,[User]=EARLIER([User])&&[WeekNumber]=EARLIER([WeekNumber])),[frequency],"occasionally"),"occasionally","Inactive"
))))),"WeekNumber",[Week],"Current frequency",[User]&": "&[frequency])
return
SUMMARIZE(result,[WeekNumber],"Detail",CONCATENATEX(FILTER(result,[WeekNumber]=EARLIER([WeekNumber])),[Current frequency]&", "))Notice: I add a method to filter the low state.
If above still not in the right direction, please feel free to let me know.
Regards,
Xiaoxin Sheng
- milpro011
Anonymous, thank you so much for taking the time. I eneded up just doing it step by step.
1. I added 5 dimensions in my original table, one for each period with the period date
2. I created 5 group queries, each grouping on a different dimension
3. I merged the 5 queries and got one master table with bucets per period