topic Dates in QlikView

Dates

Fri, 22 Oct 2010 21:04:11 GMT

Hi Everyone,

I have one quick question:I would have to add one date for startdate and one date for end date.This would be possible if I have 2 date fields but I have only one date field in my table.When I rename that only field with start date and end date I am getting he same date range as its only one date field.I have seen teh master calendar example posted on this forum but taht might not work as this table is joined with other tables using dat_key instead of date filed.Pls find the enclosed qvw for reference.

Thanks,

Swetha

Dates

Miguel_Angel_Baeyens — Fri, 22 Oct 2010 21:28:18 GMT

Hello Swetha,

Please check attached example.

Hope that helps!

Dates

Fri, 22 Oct 2010 21:39:05 GMT

Thanks Miguel for your quick response.As I mentioned above I came thru this kind of approach from forums.Now my problem how to link this kind of approach with my datamodel.I have enclosed the qv for refrence.Please review.

Thanks,

Swetha

Dates

Miguel_Angel_Baeyens — Fri, 22 Oct 2010 21:55:16 GMT

If you choose the data island, which is correct as well, first you'd need to create a end calendar date with at least the same dates you have in your master calendar, since the starting date must never be previous to the end date, I may be missing something though.

Regards.

Dates

Fri, 22 Oct 2010 22:03:45 GMT

Hi,

Thanks for the response.

But If I choose it as data Island, the dates am selecting in the calendar object are not in correspondent to the report data.Pls view the attached doc.

Thanks,

Dates

Miguel_Angel_Baeyens — Fri, 22 Oct 2010 22:13:14 GMT

Hello Swetha,

You will always need a conditional or set analysis to force the expression to return values between two dates, in your case, startdate and enddate. It's ok if you prefer to select the date from one table instead of a variable, but anyway your main fact table date has to be between two dates. So your expression may look like

sum({$<GC_BUY_OR_REDEEM={'B'},GC_ACTIVATION_TYPE={'G'}, CALENDAR_DATE = {'>=$(=Date(startdate))<=$(=Date(enddate))'}>} GC_BALANCE)

or something similar, having that both startdate and enddate are formated alike, which they seem they aren't.

If you want a chart where CALENDAR_DATE is between two dates, I'd try to adapt the variable solution to your model.

Hope that helps

Dates

Mon, 25 Oct 2010 14:33:16 GMT

Hi,

Many thanks for the reply.

I have varables only in my report and when I substituted the above formula with variable names I have used am getting the result as 0.Not sure why am getting zero.

Here is the formula I used:

sum({$<GC_BUY_OR_REDEEM={'B'},GC_ACTIVATION_TYPE={'G'}, CALENDAR_DATE = {'>=$(=Date(VstartDate))<=$(=Date(VEnddate))'}>}
GC_BALANCE)

Dates

hopkinsc — Mon, 25 Oct 2010 14:41:39 GMT

Hi, i had the same query, here are the answers i was given...

http://community.qlik.com/forums/t/32557.aspx

Dates

Mon, 25 Oct 2010 15:07:55 GMT

got it ! thanks

Dates

Miguel_Angel_Baeyens — Mon, 25 Oct 2010 15:09:55 GMT

Hello Swetha,

Just a quick question. Check if both variables and CALENDAR_DATE have the same format. If they do, then we'll have to check the expression. If they don't, then use Date() format to normalize them.

Regards