topic Re: Filter based on date in QlikView

Filter based on date

anuradhaa — Thu, 03 Dec 2015 13:23:56 GMT

I'm using below expression to get averages of previous day sales. My report reloads everyday at 07.00 AM.

avg({$<nw_date={"=nw_date>=date(ReloadTime()-1,'DD-MM-YYYY')"},type={"I"}>} ,sales)

Can you please tell me is it corrects?

i found that expression in forum. but i can't understand that expression. can you please explain that

Re: Filter based on date

sunny_talwar — Thu, 03 Dec 2015 13:26:53 GMT

avg({$<nw_date={"=nw_date>=date(ReloadTime()-1,'DD-MM-YYYY')"},type={"I"}>} ,sales)

Except for this extra comma before sales, it seems that it might work. Have you tested it out to check if it is giving you what you are looking for?

=Avg({$<nw_date={"=nw_date >= Date(ReloadTime()-1,'DD-MM-YYYY')"}, type={"I"}>} sales)

or you can try this as well

=Avg({$<nw_date={"$(='>=' &Date(ReloadTime()-1,'DD-MM-YYYY'))"}, type={"I"}>} sales)

Where this -> ='>=' &Date(ReloadTime()-1,'DD-MM-YYYY') in a text box object needs to match the format of nw_date and needs to be the date which you want.

HTH

Re: Filter based on date

Anonymous — Thu, 03 Dec 2015 13:29:20 GMT

nw_date={"=nw_date>=date(ReloadTime()-1,'DD-MM-YYYY')"}

this will return data which is greater than your last Previous Relaodtime i.e ReloadTime()-1 till today date

Re: Filter based on date

anuradhaa — Thu, 03 Dec 2015 13:33:57 GMT

can you please explain this expression

Re: Filter based on date

anuradhaa — Thu, 03 Dec 2015 13:35:36 GMT

i think that's for "nw_date>=date(ReloadTime()-1,'DD-MM-YYYY')" and what does whole thing

nw_date>=date(ReloadTime()-1,'DD-MM-YYYY') means

Re: Filter based on date

sunny_talwar — Thu, 03 Dec 2015 13:36:37 GMT

From what I understand, this is just trying to average sales for type I which is greater or equal to 1 day minus today's reload date and time. So if the application got reloaded at 10 am 12/2/2015 the expression will average sales for nw_date greater than or equal to 10 am 12/01/2015

Re: Filter based on date

anuradhaa — Thu, 03 Dec 2015 13:41:41 GMT

If i want to get Previous day data , how can i do that.

Re: Filter based on date

Anonymous — Thu, 03 Dec 2015 13:41:51 GMT

nw_date={"=nw_date>=date(ReloadTime()-1,'DD-MM-YYYY')"}

and

nw_date={">=date(ReloadTime()-1,'DD-MM-YYYY')"}

Both expression will give same result which would be from your previous day of reload to till to date

Re: Filter based on date

anuradhaa — Thu, 03 Dec 2015 13:44:33 GMT

If i want to get only the previous day, what is the best solution

Re: Filter based on date

Anonymous — Thu, 03 Dec 2015 13:46:49 GMT

Previous day of reload?

then do this:

nw_date={'=$(=date(ReloadTime()-1,'DD-MM-YYYY'))'} //simply Remove greater sign

Re: Filter based on date

sunny_talwar — Thu, 03 Dec 2015 13:49:03 GMT

Just the previous day data? Assuming you nw_date is just a date (and not a timestamp)

you can try one of these:

{<new_date = {"$(=Date((Floor(ReloadTime) - 1), 'YourDateFieldFormat')"}>}

{<new_date = {"$(=Date(Today(), 'YourDateFieldFormat')"}>}

Re: Filter based on date

anuradhaa — Thu, 03 Dec 2015 13:58:31 GMT

Grate, It works. But i have country and salesman as dimensions. And date calendar. When i select date from the calendar values get changed.

I want to know how that happens and what's that value represent.

Say i have select date in September 26 ,then if the value is 58.68 what is that value?

is it the figures for september 25th

Re: Filter based on date

Anonymous — Fri, 04 Dec 2015 05:54:36 GMT

Its totally depends on your last reloadtime()...

As you asked say you have selected Sept 26,

then if last reload of your QVW was Sept 25 then you will be getting Sept 24 data which is reloadtime()-1.

Hope this will help!!

Re: Filter based on date

anuradhaa — Mon, 07 Dec 2015 06:30:33 GMT

Hi,

When it is nw_date={'=$(=date(ReloadTime()-1,'DD-MM-YYYY'))'} and no selections it will show all the values.

but when i remove "="

nw_date={'$(=date(ReloadTime()-1,'DD-MM-YYYY'))'} it shows prev day data.

but when i change date by using selection box that value doesn't change.

Re: Filter based on date

anuradhaa — Mon, 07 Dec 2015 06:31:54 GMT

Can i change this to have rev day results based on selected date. if i select 2014-12-31 then can i have 2014-12-30 data.

and keep default as reloaddate-1