Contributor

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!

4 REPLIES 4
Highlighted
Master Anaplanner/Community Boss

Re: Boolean between two dates

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

Highlighted
Master Anaplanner/Community Boss

Re: Boolean between two dates

@fredrickstraube @usman.zia 

Takes two intermediate line items, but simpler

2019-10-14_11-01-07.png

 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

Highlighted
Contributor

Re: Boolean between two dates

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!

Highlighted
Master Anaplanner/Community Boss

Re: Boolean between two dates

Excellent!