topic Limiting Chart to show only data at most recent date in data table. in QlikView

Limiting Chart to show only data at most recent date in data table.

Sun, 23 Mar 2014 01:11:42 GMT

Is there any way I can limit a chart to only show the data in the current date (date provided on my computer)?

I have accumulated data is time stamped each day. Right now, my chart is adding all the data together but I only need to pull the data from the most recent date in my data table.

Below is my script.

[Main Data]:

LOAD [Country Name],

[Country Code],

[Sync State],

Count,

[Creation Date/Time],

Timestamp#(date(date#(mid([Creation Date/Time], 1, 16), 'YYYY/MM/DD hh:mm'), 'YYYY')) as Year,

Timestamp#(date(date#(mid([Creation Date/Time], 1, 16), 'YYYY/MM/DD hh:mm'), 'MMM')) as Month,

Timestamp#(date(date#(mid([Creation Date/Time], 1, 16), 'YYYY/MM/DD hh:mm'), 'DD')) as Day,

Timestamp#(date(date#(mid([Creation Date/Time], 1, 16), 'YYYY/MM/DD hh:mm'), 'MMM/DD')) as MonthDay

FROM

Re: Limiting Chart to show only data at most recent date in data table.

MarcoWedel — Sun, 23 Mar 2014 01:45:46 GMT

Hi,

add

date(date#(left([Creation Date/Time], 10), 'YYYY/MM/DD'), 'YYYY/MM/DD') as CreationDate

to your load script

and add the set expression

{$<CreationDate={'$(=date(now(),'YYYY/MM/DD'))'}>}

to your expressions

regards

Marco

Re: Limiting Chart to show only data at most recent date in data table.

Sun, 23 Mar 2014 01:52:01 GMT

Thank you. The only Definition I have is: sum(if([Migration State]='Attempted',Count))/1000000

Where do I add the below expression exactly?

{$<CreationDate={$(=date(now(),'YYYY/MM/DD'))}>}

Re: Limiting Chart to show only data at most recent date in data table.

MarcoWedel — Sun, 23 Mar 2014 02:10:43 GMT

try

sum({$<CreationDate={'$(=date(now(),'YYYY/MM/DD'))'}, [Migration State]={'Attempted'}>} Count)/1000000

instead of

sum(if([Migration State]='Attempted',Count))/1000000

regards

Marco

Re: Limiting Chart to show only data at most recent date in data table.

Sun, 23 Mar 2014 22:48:37 GMT

I'm sorry. This didn't work...

No values are appearing when paste this into my expression.

Re: Limiting Chart to show only data at most recent date in data table.

Sun, 23 Mar 2014 22:52:55 GMT

The formula doesn't have to be today's date (timestamp on computer) but just ensuring it's only taking data from the latest available date from the data (since the data is always cumulative).