topic Re: Re: Straight table - setting date range to view in QlikView

Straight table - setting date range to view

danielnevitt — Tue, 27 Aug 2013 15:09:35 GMT

Hi,

I have a stright table that shows; Company, Value and the min(Create_Date).

I'm trying to view only data that has been created since yesterday.

I have created a variables:

vToday = max(Create_Date)

vYesterday = max(Create_Date)-1

However I am struggling with how to use these variables to only shows companies with a min(Create_Date) of yesterday.

Any help is much appreciated.

Regards,

Daniel

Re: Straight table - setting date range to view

Nicole-Smith — Tue, 27 Aug 2013 15:44:54 GMT

Change your expression from min(Create_Date) to min({<Create_Date={'>=$(vYesterday)'}>}Create_Date). (See the attached for an example.)

Re: Straight table - setting date range to view

jagan — Tue, 27 Aug 2013 15:54:25 GMT

Hi,

Try this

sum({<Create_Date={'<=$(=Date(vYesterday))'}>}value)

Regards,

Jagan.

Re: Straight table - setting date range to view

danielnevitt — Tue, 27 Aug 2013 16:03:20 GMT

Hi Nicole,

Thank you for your reply.

I think I wasn't quite clear enough in my original message. The table should only show trades where the min create date is yesterday or greater.

As an example:

Company Create_Date

A 26/08/2013

A 27/08/2013

B 20/08/2013

B 26/08/2013

B 27/08/2013

C 20/08/2013

C 21/08/2013

C 22/08/2013

I am trying to produce a table that shows the following as a result:

Company Create_Date

A 26/08/2013

This is because company A is the only company that has a minimum (first) create date of yesterday or greater.

If you are able to help with the above, that would be great.

Thanks,

Daniel

Re: Re: Straight table - setting date range to view

Nicole-Smith — Tue, 27 Aug 2013 16:25:06 GMT

In that case, update your Company dimension to if(aggr(min(Create_Date), Company)>=today()-1, Company) and check Suppress Values When Null. (See attached.)

Re: Re: Straight table - setting date range to view

danielnevitt — Tue, 27 Aug 2013 16:32:45 GMT

Thank you so much for your help.

Regards,

Daniel