Time Series and Parameters

[Anonymous]

Hi,

I am basically loading data from a time series table, with the ffg in SQL:

Select EffectiveDate, Value1, Value2, StartDate,EndDate

From X,Y

WHERE  Y.EffectiveDate = @EffectiveDate

               AND @EffectiveDate Between X.StartDate and X.EndDate.

The problem is when I read into Qlikview without the, WHERE clause of cause, since I want to slice on EffectiveDate in Qlikview.

The EffectiveDate works but not the StartDate and EndDate.

Does anybody have an idea on how to get mimic this WHERE clause into Qlikview.

THANKS!!!

[CELAMBARASAN]

Hi,

     If the above query works in SQL then it will also work in qlikview.You can also try with SQL before select statement.

SQL Select EffectiveDate, Value1, Value2, StartDate,EndDate

From X,Y

WHERE  Y.EffectiveDate = @EffectiveDate

               AND @EffectiveDate Between X.StartDate and X.EndDate.

Which database vendor you are using?

Celambarasan

[jagan]

Hi,

Is @EffectiveDate is a variable or Column name, if it is a column name it definitely works.

Regards,

Jagan.

[Anonymous]

Hi,

@EffectiveDate is a Parameter.

In SQL I have this query

Select EffectiveDate, Value1, Value2, StartDate,EndDate

From X,Y

WHERE  Y.EffectiveDate = @EffectiveDate

               AND @EffectiveDate Between X.StartDate and X.EndDate.

In Qlikview I have:

LOAD *;

SQL Select EffectiveDate, Value1, Value2, StartDate,EndDate

From X,Y

I have removed the WHERE clause because I put the EffectiveDate in a listbox so I can slice the data

based on EffectiveDate, it basically mimics  the WHERE Y.EffectiveDate = @EffectiveDate  .

But this does not give me the correct values for the StartDate and EndDate,

I cannot find a way to mimic WHERE @EffectiveDate Between X.StartDate and X.EndDate.

[CELAMBARASAN]

Hi,

     Where your going to display this(Which chart type and dimensions) and what information?

     Can you give me a brief about your problem if i didn't understand clearly about your problem?

     Solution to you based on my understanding

     Create Straight table.

     You can have

     EffectiveDate, Value1, Value2, StartDate,EndDate as dimension

     Expression as =if(StartDate>=Max(EffectiveDate) AND EndDate<= Max(EffectiveDate),1)

     Go to Presentation Tab of the chart properties use Hide Column option for the above Expression.

Hope it helps

Celambarasan

[deepakk]

hi,

You can do this in two ways.

Method 1 : Create calendar , year, month and day for the effective date and select the required days

eg select 2011 , jan and days as 1 to 10..

This will work same as effectivdate >= 01-Jan-2011  and  <= 10-Jan-2011.

The disadvantage of the above case is selecting dates between two months like 15th jan to 15th Feb.

for such cases you can go by method2

Create a calendar object and use two variables inside your reports.

I have attach a sample file . Ihope  that helps

Deepak

[Anonymous]

Now when I choose EffectiveDate = '31-12-2011'

I want StartDate and EndDate to automatically filter i.e StartDate < EffectiveDate and EndDate> EffectiveDate.

In this case the only possible value for StartDate is '21-12-2011' and EndDate is '03/01/2012'.

A StartDate such as '16-12-2011' is not a possibility, even though it is less than the EffectiveDate, since it's EndDate is '20-12-2011'.

[CELAMBARASAN]

Hi,

     This is default QV feature it shows the possible values based on the logical links between Startdate and end Date with the Effective date.

     Its like what all the startdates and end dates that matched with the effective date.

     In Query

SQL Select EffectiveDate, Value1, Value2, StartDate,EndDate

From X,Y

WHERE  Y.EffectiveDate = '31-12-2011'; this is what QV does it equals the selection to the same field in that way only List boxes designed.

You Can Do this by expression i think

Check with this

=Aggr(if(StartDate>=Max(EffectiveDate) AND EndDate <= Max(EffectiveDate),StartDate),StartDate,EndDate) for Start date list box.

=Aggr(if(StartDate>=Max(EffectiveDate) AND EndDate <= Max(EffectiveDate),EndDate ),StartDate,EndDate) for End date list box.

But it shows possible values only.

Celambarasan

[deepakk]

hi ,

If you can attach a sample data , then it would be able to provide a work around.

Deepak

[deepakk]

hi,

I am not sure but  you can create the start date and end date from the script and check

Date(num(if( num(StartDate) <= num(EffectiveDate) and EndDate >=  num(EffectiveDate) ,StartDate)),'DD-MM-YYYY') as StartDate

Date(num(if( num(StartDate) <= num(EffectiveDate) and EndDate >=  num(EffectiveDate) ,EndDate)),'DD-MM-YYYY')

as EndDate

I hope this helps.

Deepak