How to code 'not between' in an expression

[Anonymous]

Hello! Thank you for taking the time to read and (potentially!) help me out! I am currently writing a program where I pull in a chart all the instances that happen within a certain time span (start_time and end_time).  However, I would like to know what percentage happens outside this span.  Does anyone know how to code this in an expression? Or how I could show this numbers (divided up by fields) in a chart?

One example: Start time is 30 sec and end is 90 sec.  I want to know what percentage happens for countries A, B, and C OUTSIDE of this timestamp (so less than 30 sec and greater than 90 sec).

Thanks for your time!

[neelamsaroha157 · Accepted solution]

May be you can try something like this

=Count({$<Start_Time={"<30"}, End_Time={">90"}>}Your_Measure)

Your can replace 30 & 90 with your with variable or expression..

[ziadm]

Set Analysis above by neel should do another faster way to flag those that are out of the boundaries < 30 and > 90 in your load script as 1

load

if ( Start_Time <30 and End_Time >90,1,0) as Boundry_flag

then your Bar chart expression 

Dimension = Country

Expression1 =  Count ({$<Boundry_Flag = {1}>}, No)    // Out of boundaries

Expression2 =  Count ({$<Boundry_Flag = {0}>}, No)   // Within boundaries

for Pie Chart

Dimension = Boundry_flag   // Make this calculated to name the Dimension

Expresion = Count(No)

[Anonymous]

Hi Ziad,

Thank you for your response! My concern with putting that code in the load script is how does it work when I define the varaibles in the sheet and not the load script. The start_time may not always be 30, and so how would the user be able to edit it later?  Also, what is the point of the {1} and {0}?

Thank you!

[ziadm]

Hi Natascha

You could have your variable in an excel file such as

Variable         Definition

Start_Time          30

End_Time               90

Load your variable excel file before you load the data

Variables:

LOAD Variable AS Name,

     Formula AS Definition

FROM [..\Data\variableFile.xlsx] (ooxml, embedded labels, table is Sheet1);

(ooxml, embedded labels, table is Sheet1);

Let vNumberOfRows = NoOfRows('Variables');

For vI = 0 to (vNumberOfRows - 1)

Let vVariable_Name = Peek('Name',vI,'Variables');

Let [$(vVariable_Name)] = Peek('Definition',vI,'Variables');

Next

DROP TABLE Variables;

Now you have these variables ready to use

{1} is a set analysis condition to count records that are flagged 1 out of boundaries

Count ({$<Boundry_Flag = {1}>}, No)