Dates

[#1]

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

[Miguel_Angel_Baeyens]

Hello Swetha,

Please check attached example.

Hope that helps!

[#3]

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

[Miguel_Angel_Baeyens]

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.

[#5]

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,

[Miguel_Angel_Baeyens]

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

[#7]

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)

[hopkinsc]

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

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

[#9]

got it ! thanks

[Miguel_Angel_Baeyens]

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