Displaying Days Delayed

[#1]

Hi Guys,

I need help regarding my table. (PSA)

I have to put an input box which will filter the days delayed.

ex:

If I type 2, it will filter all the dates that have 2days delayed.

Any ideas?

Thanks,

Donna

[sujeetsingh]

Donatzz,

Go for a set analysis and use calculated dimension as

if(inputvariable>0,only({$<Delay=$(inputvariable)>} id)>,id)

something like this,

[#3]

please see attached qvd. I don't have a field for Days Delayed and it is only computed.

[stevedark]

Hi there,

Whilst it would be possible to write an expression that works on the computed value for delay it would be more robust to have that value in the data model.  In order to achieve this I would suggest a join in your SQL statement onto a grouped sub-query.

You would have something like:

LOAD
     id,

      date,

      firstdate,

      floor(date - firstdate) as delay

      ;

SQL SELECT

      o.id,

      o.date,

       f.firstdate

FROM orders o

LEFT OUTER JOIN

(

   SELECT

       id,

       min(date) as firstdate

    FROM orders

    GROUP BY id

) f

ON f.id = o.id

With the hard work done in the load script you could just have a simple selection on the number of days delayed, or could use set analysis on a variable if you wished.

Hope I have understood your query correctly and this is of help.

- Steve

http://www.quickintelligence.co.uk/

[#5]

Thank you for the reply. Sadly in my script I only have one date field which makes it harder. Thank you

[stevedark]

Hi there,

In the example I gave the field 'firstdate' doesn't exist in the source database, it is derived at the point the load script is run from the other date field.

The line that does this is:

min(date) as firstdate

In the QVW you uploaded there was a SQL connection, so I presumed you would be able to construct a similar SQL statement in your load.

If your data is not in a SQL database then you can do similar using a resident load and a JOIN or ApplyMap.

Hope that helps,

Steve

http://www.quickintelligence.co.uk/

[#7]

thanks, is using min(date) considers what specific id the record have?

I don't know how it works, or it will check the min date of all records. if that happens, it won't work since the 1st date and the last date to be subracted should be depending on a specific id.

[stevedark]

Hi Donna,

That firstdate field will be derived for each id in the sub query, because

of the GROUP BY in the SQL statement. This is a standard SQL function - so

I would recommend Googling it and reading up on the syntax.

- Steve

http://www.quickintelligence.co.uk/