Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Expression help timestamps

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

qliklizzy

Creator II

2015-06-18
11:32 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Expression help timestamps

Hi

I have this Expression set up to give me the % of lists that finish more than 30 minutes late.

which works fine BUT I need to get it to show it in the opposite way and either side of the end time.

basically I need to to show **% of lists that finish 30 minutes within the session end time**, so either 30 minutes before and up to schedule end or 30 minutes after.

hope i havent confused anyone i'm doing it to myself very nicely though

(frac(MaxOperationEnd) < frac(Time(MaxSessionEnd) + Num(Timestamp(0.04166667) /60 * 30)),1,0))/ count({<OperationOrder={1},MaxOperationEnd={"<>''"}>} EpisodeID)

521 Views

1 Solution

Accepted Solutions

swuehl

MVP

2015-06-19
06:58 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Yes, I think by the use of copy & paste, we missed the acutal THEN / ELSE branches of your if() statement.

To get the functions and bracketing correct, it's often good to set the cursor right before a bracket, then the pairing closing / opening bracket will be highlighted. Also the tooltip indicating the syntax of functions is helpful in debugging an expression.

Try this

sum({<OperationOrder={1},MaxOperationEnd ={"<>''"}>} {<CancelledOnDay={0}>}

if(

frac(MaxOperationEnd) < (frac(MaxSessionEnd) + interval#('0:30','h:mm'))

and

frac(MaxOperationEnd) > (frac(MaxSessionEnd) - interval#('0:30','h:mm'))

** ,1,0**

)

)

/

count({<OperationOrder={1},MaxOperationEnd={"<>''"}>} EpisodeID)

400 Views

6 Replies

swuehl

MVP

2015-06-18
11:39 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Are you missing something like sum(if( ... at the front of your expression you've posted?

anyway, I think you need something like

(

frac(MaxOperationEnd) < (frac(Time(MaxSessionEnd) + interval#('0:30','h:mm'))

and

frac(MaxOperationEnd) > (frac(Time(MaxSessionEnd) - interval#('0:30','h:mm'))

)

qliklizzy

Creator II

2015-06-18
11:51 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi

thanks for that I have tried it and it sounds like thats the kind of thing i need;

but adding it in it hasnt worked it could be the brackets ?:

(sorry i did leave the first part off)

sum**(**{<OperationOrder={1},MaxOperationEnd ={"<>''"}>} {<CancelledOnDay={0}>}

if**(**frac(MaxOperationEnd) < **(**frac(Time(MaxSessionEnd) + interval#('0:30','h:mm')) and frac(MaxOperationEnd) > **(**frac(Time(MaxSessionEnd) - interval#('0:30','h:mm'))/

count({<OperationOrder={1},MaxOperationEnd={"<>''"}>} EpisodeID)

400 Views

swuehl

MVP

2015-06-18
12:18 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Yes, seems that you missed some closing brackets:

sum**(**{<OperationOrder={1},MaxOperationEnd ={"<>''"}>} {<CancelledOnDay={0}>}

if**(**

frac(MaxOperationEnd) < **(**frac(MaxSessionEnd) + interval#('0:30','h:mm'))

and

frac(MaxOperationEnd) > **(**frac(MaxSessionEnd) - interval#('0:30','h:mm'))

)

)

/

count({<OperationOrder={1},MaxOperationEnd={"<>''"}>} EpisodeID)

You don't need to format your MaxSessionEnd using Time() function.

Your set expression in the outer sum() looks kind of strange, but I guess that's another topic.

400 Views

qliklizzy

Creator II

2015-06-19
04:29 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

thanks again ...its still not working i think i have too many brackets now. its underlining the 3rd bracket after the second interval bit .. ?

thanks sorry to be a pain

wish I understood this more

400 Views

swuehl

MVP

2015-06-19
06:58 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Yes, I think by the use of copy & paste, we missed the acutal THEN / ELSE branches of your if() statement.

To get the functions and bracketing correct, it's often good to set the cursor right before a bracket, then the pairing closing / opening bracket will be highlighted. Also the tooltip indicating the syntax of functions is helpful in debugging an expression.

Try this

sum({<OperationOrder={1},MaxOperationEnd ={"<>''"}>} {<CancelledOnDay={0}>}

if(

frac(MaxOperationEnd) < (frac(MaxSessionEnd) + interval#('0:30','h:mm'))

and

frac(MaxOperationEnd) > (frac(MaxSessionEnd) - interval#('0:30','h:mm'))

** ,1,0**

)

)

/

count({<OperationOrder={1},MaxOperationEnd={"<>''"}>} EpisodeID)

401 Views

qliklizzy

Creator II

2015-06-19
07:38 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

thank you so much for spending time on this for me - perfect!

400 Views