topic Re: Dynamic last 15 days in QlikView

Dynamic last 15 days

Mon, 02 Sep 2013 14:50:12 GMT

Hello,

I have a line chart where i have:

Date as dimension

Expression as:

=sum({<Date_Clean={'$(vLast15Days)'}>} TotalPrice)

This gives me from today and 15 days back.

What if i want to make this dynamic, so if i select the 27 of august, it shows me 27 of august and 15days back.

Now its just static and i cannot select a date back in time.

I have these listboxed to choose:

Month, Year, Day

Re: Dynamic last 15 days

Clever_Anjos — Mon, 02 Sep 2013 14:53:33 GMT

What´s the content of vLast15Days?

Re: Dynamic last 15 days

Mon, 02 Sep 2013 14:55:29 GMT

Its

='>=$(vPast15Days) <=$(vDateToday)

where

vDateToday = num(Date(Today(), 'YYYY-MM-DD'))

and vPast15Days = num(date(vDateToday-15, 'YYYY-MM-DD')

Re: Dynamic last 15 days

Clever_Anjos — Mon, 02 Sep 2013 15:09:17 GMT

Create another variable and attach a calendar to it

Change your expressions

='>=$(vPast15Days) <=$(vDateToday)

where

vDateToday = num(Date(vNewVariable, 'YYYY-MM-DD'))

and vPast15Days = num(date(vNewVariable-15, 'YYYY-MM-DD')

Re: Dynamic last 15 days

Mon, 02 Sep 2013 15:15:15 GMT

Im not sure i follow you.

Attach a calendar to it?

I already have a mastercalendar.

Re: Dynamic last 15 days

MayilVahanan — Mon, 02 Sep 2013 15:47:12 GMT

Try like this

=sum({<Date_Clean={">=$(=Date(Max(Date_Clean)-15))<=$(=Max(Date_Clean))}>} TotalPrice)

Hope it helps

Re: Dynamic last 15 days

MEllinghausen — Mon, 02 Sep 2013 15:47:38 GMT

you should set your variable vdatetoday to num(date(year &'-'&month&'-'day) if you dont have the dimension day like '2013-08-04'

Marcus

Re: Dynamic last 15 days

Mon, 02 Sep 2013 15:48:00 GMT

Hi,

Try this,

=sum({<DateNum= {'>=$(=(Num(Date(Date, -15))))<=$(=(Num(Date(Date))))'}>} TotalPrice)

Re: Dynamic last 15 days

Mon, 02 Sep 2013 15:51:47 GMT

sum({<Date_Clean={">=$(=(Max(Date_Clean)-15))<=$(=Max(Date_Clean))"}>} TotalPrice)

If i do like this: I will get the last 15 days, and i can select dates (but it will only show the selected date)

Re: Dynamic last 15 days

Mon, 02 Sep 2013 15:52:12 GMT

my dimension date is like '2013-08-04'

Re: Dynamic last 15 days

Mon, 02 Sep 2013 15:53:33 GMT

does not work 😕

Re: Re: Dynamic last 15 days

MEllinghausen — Mon, 02 Sep 2013 16:23:50 GMT

Hallo Thomas,

please look to the attached file

Marcus

Re: Dynamic last 15 days

Clever_Anjos — Mon, 02 Sep 2013 16:26:44 GMT

Object type Calendar/Slider

Re: Dynamic last 15 days

Mon, 02 Sep 2013 16:59:53 GMT

I see your point, but when i make a variable called date = Date_clean my inputbox just shows an -

But when i make the same variable in my QVD load, it works fine?

Re: Dynamic last 15 days

MayilVahanan — Mon, 02 Sep 2013 17:03:49 GMT

Try like this

DateFormat must be same.

=sum({<Date_Clean={">=$(=Date(Max(Date_Clean)-15,'YYYY-MM-DD'))<=$(=Date(Max(Date_Clean),'YYYY-MM-DD'))}>} TotalPrice)

Re: Dynamic last 15 days

Mon, 02 Sep 2013 17:06:15 GMT

My date format is the same:

In my main script it is YYYY-MM-DD

My Date_Clean dates are YYYY-MM-DD

converted to num(Date_Clean) So they are numeric

Re: Dynamic last 15 days

Mon, 02 Sep 2013 17:13:38 GMT

heres how my dates look like:

Re: Dynamic last 15 days

Mon, 02 Sep 2013 17:35:06 GMT

Are u able to make it so it start with this day and 15 back, and then when i select a date it goes 15days back from that date, cause now it shows all values untill u select a date

Re: Dynamic last 15 days

Mon, 02 Sep 2013 17:36:17 GMT

The other strange thing is:

In my QVDload - where i load from QVD files, i can do it and i can even make a variable called vDate = Date_Clean

which gives me a number, but when i does it in my BINARY load qvw nothing happens

Re: Dynamic last 15 days

MayilVahanan — Mon, 02 Sep 2013 20:12:11 GMT

Can you provide a sample file?