Hi QS Community. I am working a project that involves counting the % of accounts that have at least one activity per month from the customer success manager.
This metric has proven to be very difficult to calculate and I wanted to see if anyone has experience/advice on this.
Field returning 1 any account with 1 or more activities in a given month - call it “1 Activity.”
I have tried counting the number of activity ids and then creating an additional calculated field that says if # of activities >=1 then 1 else 0. I cannot aggregate that field. I’ve tried dividing number of activities by the number of activities and I cannot aggregate that either.
The ideal formula would be something like this: sum(1 Activity)/#of accounts
I have provided a screenshot of my issue. You can see that the ifelse() statement gets the right idea but the aggregation is wrong. This then throws off all the calculations which is why you get 400% instead of 100% in the total rows instance. It’s dividing 32/8 instead of 8/9.
I can think of below mentioned solution, based on the assumption that the data is at account - time period level.
step 1: tweak the if else to return account id or null instead of the number 1or 0
step 2: tweak the percentage formula to distinct count of activity>1 as calculated in step 1 / distinct count of account. This will supposedly give correct values even when aggregated and not calculated at account level.
However, If you can mask or simulate sample data and share in the arena, it would be best to understand the data granularity that you are dealing with and share an accurate formula for the calculation.
Thanks for helpin me out. I couldn’t figure out how to make the ifelse() function to work. I am new to QS so i’m trying to figure out the best way to model data. Right now this should work.
Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!
