Solved

Create formula to have result = null

  • 9 March 2020
  • 6 replies
  • 94 views
T
Rank
Userlevel 1
Badge +2

Hello - 

 

We have a metric that we track regarding logins, and we want it to show as blank or null if there are no active users yet because technically they could have a 0% login rate with active users which is different than 0% with no active users. I’m trying to create a calculated field on the company object.

I’ve tried many different versions. All so far will show 0% even when there are no active users. I’ve tried =null, is not null, =0, ...

case when Active_Users is null then null else Unique_Login / Active_Users end

Thanks

icon

Best answer by phani_kumar 3 April 2020, 10:50

View original

6 replies

phani_kumar
Rank
Userlevel 5
Badge +3

Hi Tammy.

I am assuming you can achieve the task with rules using Formula fields.

But can you please provide more information with examples if possible on the task which you want to achieve. So that we can provide a better solution or workaround.

sai_ram
Rank
Userlevel 7
Badge +1

@tammy_c did you get a chance to view the comments by @phani_kumar.

T
Rank
Userlevel 1
Badge +2

Hi, sorry about that, ironically, I’m busy trying to get Gainsight rolled out. Thanks!

@phani_kumar

There isn’t much more detail than what I had posted, but let me know if this helps.

We have a login rate that we track which is “# of unique logins” for a time period / “# of active users”.

Easy enough formula at its base, but we want it to show as blank or null if there are no active users, not 0 because technically a 0% login rate for a customer of ours that has active users is much different than a 0% rate for a customer that hasn’t activated any users yet. It’s a rare case, but was trying to cover it anyway. 

I know I could do it with rules, but I wanted to be able to use a formula if possible so a rule wouldn’t have to be run. I also was just trying to learn formulas as well.

Thanks!

phani_kumar
Rank
Userlevel 5
Badge +3

Hi Tammy.

I am assuming you have 2 Objects in your Source. One is for to store the Actual Users Information and Another Object is for to store your Usage Data(# of unique logins and # of active users)

 

Fetch these 2 objects in a rule and do a merge task based on the common field between your objects. And the join Condition should be Left Join and the Left Object was your Actual Users Object no the Usage Object.

 

For example, in your first fetch, you have 10 records and in your Usage fetch, you have 5 records.

So in the Merge task, you will get the 10 records along with the once which are matched in the Usage Data fetch task. So obviously you will get the Null only in the filed for the extra 5 users which have not existed in the Usage data.

Fetch one:

User ID
100
101
102
104
105

 

Fetch two

User ID No Of Active Users
100 5
102 10
104 16

 

Now after merge, you will get the result as below:

User ID No Of Active Users
100 5
101 NULL
102 10
104 16
105 NULL

 

I hope this will trick will work.

Thanks.

sai_ram
Rank
Userlevel 7
Badge +1

@tammy_c did you get a chance to view comments posted by our team?

T
Rank
Userlevel 1
Badge +2

Thanks. Yes, I saw it. I was trying to do it just with a calculated field. I knew I could probably do it with rules, but wanted to it to not require running a rule.

Reply


Terms & ConditionsCookie settings