topic Re: please help ive been stuck for a while :( in QlikView

please help ive been stuck for a while :(

samuel_brierley — Tue, 03 Sep 2013 07:35:14 GMT

what in my excel source file in each row there is a collumn for date/time item went offline and a collumn when it came back so what the formula needs to do is show all items where the date/time went offline is greater than date specified and date/time is less than date/time specified. thanks

Re: please help ive been stuck for a while :(

dmohanty — Tue, 03 Sep 2013 07:37:49 GMT

Could you please bit more clear on your requirement (last line) and attach the Excel Data Source ?

Re: please help ive been stuck for a while :(

samuel_brierley — Tue, 03 Sep 2013 07:43:06 GMT

Dropbox - qlikview.xlsm

I want to be able to select any date/time and the chart will show how many assets where off line at that time, I can show how many went online or how many came back but not how many where offline.

the attached file is only a few of these entries and ignore the table.

Re: please help ive been stuck for a while :(

Tue, 03 Sep 2013 08:04:43 GMT

Hi Samuel,

Do you have a master calendar in your data model? I'd start by adding one of those. You can then link your different dates to your master calendar via a link table.

If I've understood your problem correctly, what you really want to be able to see is all of the machines that were down when you select a specific date i.e. that have no record of the particular date selected associated with them. Rather than create a record for each day a machine was down, I think the answer will lie in a set analysis expression that will select excluded records based on the date you have selected.

How are you going to handle machines that were detected and cleared on the same day? i.e. from the chart in your attachment, if you selected the 3rd Jan 2013 what would you want to see from the top 3 lines?

Andy

Re: please help ive been stuck for a while :(

samuel_brierley — Tue, 03 Sep 2013 08:14:54 GMT

yes i do have a master calender, what im thinking is some kind of sumif that for every day it count how many went offline before this date minus all thos that came back before this date but this would need to be done in that script page and i havent the foggiest how to do that.

in regard to those that went out and came back on the same day I would like them to be shown as offline for that day and they essentially come back the next day, preferably I would like as i zoom in on the calender the dimension changes to time stamp so i can see those that went offline and came back but im sure this would be too complicated and even if you where able to do it I wouldnt be able to replicate lol

thanks

Re: please help ive been stuck for a while :(

Tue, 03 Sep 2013 08:24:59 GMT

I wouldn't create that in the script. That sounds more like a set analysis expression to me. Your expression would be something along the lines of (will need tweaking to fit your model):

(Count({$< [Date Cleared] = {"<=$=(Max(CalendarDate))"}>}DeviceAddress)-

(Count({$< [Date Detected] = {"<=$=(Max(CalendarDate))"}>}DeviceAddress)

As for your drill down on your date/time dimension, you could create a drill down group along the following lines:

month,

day,

hour,

minute

etc

Use that drill down group as a dimension in your chart and you should be on the right track.

Andy

Re: please help ive been stuck for a while :(

samuel_brierley — Tue, 03 Sep 2013 09:05:13 GMT

I want to say thank you for helping and apologise for my uselessness.

({$< [Date Cleared] = {"<=$=(Max([Interval Date]))"}>}[Fault Id])-

Count({$< [Date Detected] = {"<=$=(Max([Interval Date]))"}>}[Fault Id])

I have twekaed your formula slightl to include interval date (this is the name of a collum that is in my master calender that copies the date that is used as a key)

I have tried all the different date collumns in them boxes and all result in "no data to display"

Re: please help ive been stuck for a while :(

Tue, 03 Sep 2013 09:22:48 GMT

Don't apologise!!

It's probably a syntax error on my part :-). If you are handling multiple date types, I would create a calendar link table. I have found these incredibly useful in the past. What I noticed from looking at your excel is that your interval date always seemed to be 4/01/2013 regardless of the detected or cleared date You can create one as below:

CalendarLink:

LOAD

PrimaryKey, (must be a unique value in your main table)

Date Detected as Date (or whatever your autogenerated date field is called in your master calendar)

0 as DateType (to enable you to refer back to the original date types)

Resident Main Table;

Concatenate (CalendarLink)

LOAD

PrimaryKey

Date Cleared as Date

1 as DateType

Resident Main Table;

Repeat for as many date types as you need to link to a calendar.

The primary key will link back to your main table and the date field should link to your master calendar. You can still refer to a particular date type using the datetype flag i.e.

Count({$<DateType = {1}, [Date Cleared] = {"<=$=(Max([Interval Date]))"}>}[Fault Id])-

Count({$<DateType = {0} [Date Detected] = {"<=$=(Max([Interval Date]))"}>}[Fault Id])

In terms of fixing the formula break it down so you get each part working at a time and make sure the value you are searching for ie your max date is valid. I find inserting parts of complicated set analysis expression into text boxes useful to test them . I'd probably also create a variable called vMaxDate which would be Max(Date). Then your formula would read:

Count({$<DateType = {1}, [Date Cleared] = {"<=$=(vMaxDate))"}>}[Fault Id])

Also read this post titled Set Analysis - Select dates less than date in selection - http://community.qlik.com/thread/38690

Andy