IF statement determined by boolean

  • 11 June 2024
  • 1 reply


Hello - I am trying to update a formula based on a boolean that is determined by the latest date in a data upload. I believe the following equation is close to what I need.

Formula: “if('Is Forecast?','Go Forward Cohort'[select:'Go Forward LTV Driver'."Borrower Retention"][BY:->'Map to Cohort Month'] , if('Is Actual?',('2.002 Cohort View - Borrower Retention Lookup')[remove lastnonblank: Month][remove lastnonblank: Cohort][ADD: Cohort]))”

There are two issues:
1. I want to add a previous function to the first part of the if statement. If there is no data to pull from the “go forward cohort metric” I want the function to pull the last data point there is

2. When updating to the above formula my application will time out. Is there a more efficient formula that will prevent this? 

Thank you!

1 reply

Userlevel 3
Badge +6

Hi Matt,

1. For your 2 points of concern, I suggest you to break down your complex formula, then add the PREVIOUS/SELECT(dimension - 1) in a separate metric, so it’s easier for you to audit/ validate data, and also, reduce the calculation time of each metric and therefore stay below the timeout threshold.

Based on my personal experience, to avoid time-out, be very careful with IF function, I always maintain my best practice to apply “BLANK” in my IF formula for the arguments I expect to generate blank values, to avoid outcome that will densify my metric

2. Besides that, looking at your formula, this is my guess:
- I see that you ADD CONSTANT the Cohort after REMOVE LASTNONBLANK it, is it because you want to allocate the lastnonblank value to all the current Cohort ?
[remove lastnonblank: Month][remove lastnonblank: Cohort][ADD: Cohort]))
My guess is that, maybe there are a lot of items in your Cohort list, then when you ADD CONSTANT Cohort, your metric is very densified;

- Also, [BY:->'Map to Cohort Month'] with this BY: →  syntax and the name “Map to Cohort Month”, I think the outcome of the first IF statement will be dimensioned by at least Month and Cohort, meanwhile the outcome of the second IF statement is not dimensioned by Month as you already removed Month, but Pigment will still auto ADD CONSTANT Month in this case as per your first IF statement, and maybe it makes your metric even more densified, , that’s why it got timed out.

Hope my answer helps.