Boolean between two dates

Hi,

I have a module with three line items: "From", "To" and "Any", which are all Boolean formatted. I also have time as a dimension. "From" is just true for one month, which comes from another module, and the same goes for "To".

My problem is within the "Any". I want that to be true for all dates between the "From" and "To". For example, if "From" is Jan 20, and "To" is Oct 20, I want all month between Jan and Oct to be true.

Any help is highly appreciated!

Accepted answers

DavidSmith

@fredrickstraube @usman.zia

Takes two intermediate line items, but simpler

From? Cumulate = From? OR PREVIOUS(From? Cumulate)

To? Cumulate = To? OR PREVIOUS(To? Cumulate)

Any? = From? Cumulate AND To? OR NOT To? Cumulate

Caveat is you do need to ensure From? and To? are populated. It assumes if no To?, then it is included but you could add in a condition to check if there was no value for To? for the year to stop that if needed

I hope that helps

David

All comments

usman.zia

Hi @fredrickstraube

It is possible to simply this by using a formula like this.

Where "Jan 20" and "Oct 20" are not time dimensioned line items in period format.

IF Oct 20 = (ITEM(TIME)) Then TRUE ELSE IF Jan 20 = (ITEM(TIME)) THEN TRUE ELSE IF Jan 20 < (ITEM(TIME)) AND OCT 20 > (ITEM(TIME)) THEN TRUE ELSE FALSE

I hope this helps!

Thanks,

Usman

DavidSmith

@fredrickstraube @usman.zia

Takes two intermediate line items, but simpler

From? Cumulate = From? OR PREVIOUS(From? Cumulate)

To? Cumulate = To? OR PREVIOUS(To? Cumulate)

Any? = From? Cumulate AND To? OR NOT To? Cumulate

I hope that helps

David

fredrickstraube

Hi,

Thanks a lot for your help, it worked fine. However, I really wanted to do this in one line item, and managed to get it to work. This is what I did:

IF 'From' THEN 'From' ELSE IF PREVIOUS('To') THEN 'From' ELSE PREVIOUS('Any')

Cheers!

DavidSmith

Excellent!

AdamE

Hi David, Thanks for this solution, it is exactly what I need for a problem. However I have followed your solution and am getting ticks in the Any? Line item prior to the From? Line item, my objective is to have ticks between 13 and 19th Jan. Thanks in advance

Screenshot_2021-01-14_at_5_95130.png

rob_marshall

@AdamE

I found the same thing as you...Try this, it worked for me:

Basically, I changed the the following:

Cumulate to: To or Next(Cumulate To)

Final: Cumulate From and Cumulate To

AdamE

Hi @rob_marshall

Thank you, yes that does the trick.

Kind regards

Adam

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…