Hi, I am new to quicksight. I need some help to make a calculate field. The basic requirement is to get “% of users taking a practice test more than one time” (289/total user).
So, by using visualization I am able to get the total 289 count of users. but I am unable to achieve the using a single calculated field.
INFORMATION: I am using 3 columns. CMSUSERID, RESOURCEID, EVENTTYPE(‘Score Test’).
Please refer the below screenshot of analyses.
Are you saying you want 289 on all of your rows?
Or are you saying you want the count to be 289 without having to filter?
For the second option you can do this.
countIf({CMSUSERID}, {EVENTTYPE}='Score Test' AND countOver({CMSUSERID},[{CMSUSERID},{RESOURCEID}],PRE_AGG)>1)
Hi @Max, thank you for your reply.
your logic is correct but I am getting 4737 counts. Here are a few more details.
The total distinct user count is 891.
The distinct user count who has ‘Score Test’ event type is 329 group by resourceid.
The distinct user count who has ‘Score Test’ even type more than 1 time group by resourceid is 289.
using your calculated field, it is giving me 4737.
countIf({CMSUSERID}, {EVENTTYPE}=‘Score Test’ AND countOver({CMSUSERID},[{CMSUSERID},{RESOURCEID}],PRE_AGG)>1) = 4737
distinct_countIf({CMSUSERID}, {EVENTTYPE}=‘Score Test’ AND countOver({CMSUSERID},[{CMSUSERID},{RESOURCEID}],PRE_FILTER) > 1) = 329
distinct_countIf(CMSUSERID, countOver(CMSUSERID,[CMSUSERID,RESOURCEID],PRE_AGG) > 1) = 289 with using filter EVENTTYPE equals to Score Test.
I need % of users. So, I need to divide the 289 counts with 891. I am able to achieve the count(289) in one calculated field but as here I am using EVENTTYPE filter, if I add another calculated field for distinct users, it is dividing users by 329 instead of 891.
If you don’t add eventtype=‘Score Test’ to this one does it get you 891?
distinct_countIf({CMSUSERID}, {EVENTTYPE}=‘Score Test’ AND countOver({CMSUSERID},[{CMSUSERID},{RESOURCEID}],PRE_FILTER) > 1)?
If you don’t add eventtype=‘Score Test’ to this one does it get you 891? → Yes
distinct_countIf({CMSUSERID}, {EVENTTYPE}=‘Score Test’ AND countOver({CMSUSERID},[{CMSUSERID},{RESOURCEID}],PRE_FILTER) > 1)? → 329
Great! Can you use that calculated field?
No sir, I can’t use the same calculated field(891 count), as I have already applied filter to the parent calculated field(289).
To be more clear I need 289(score test filter)/891(total cmsuser count).
Why do you need to apply the filter?
Will the 289 be 289 if you take off the filter?
No sir, if i remove the filter then the count increase to 329.
Why don’t you add a condition of if event type is equal to Score Test as well in the calculated field?
Because even if I add event type is equal to the score test, it is giving me 329 count. so, If I add it as a filter then it gives me desired count 289.
To confirm you’ve made a calculated field that says this.
distinct_countIf(CMSUSERID, {EVENTTYPE}=‘Score Test’ AND countOver(CMSUSERID,[CMSUSERID,RESOURCEID],PRE_AGG) > 1)
And it is not what you are looking for?
You also will need to add the eventtype in your countOver.
distinct_countIf(CMSUSERID, {EVENTTYPE}=‘Score Test’ AND countOver(ifelse({EVENTTYPE}=
Score Test', CMSUSERID,NULL),[CMSUSERID,RESOURCEID],PRE_AGG) > 1)