topic Re: How to code 'not between' in an expression in QlikView

How to code 'not between' in an expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: How to code 'not between' in an expression

neelamsaroha157 — Thu, 03 Nov 2016 17:23:20 GMT

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..

Re: How to code 'not between' in an expression

ziadm — Thu, 03 Nov 2016 17:39:08 GMT

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)

Re: How to code 'not between' in an expression

Anonymous — Fri, 04 Nov 2016 09:49:46 GMT

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!

Re: How to code 'not between' in an expression

ziadm — Fri, 04 Nov 2016 16:23:06 GMT

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');

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)