Adding Datenum to calendar invalidates all formulas?

[#1]

Hi,

I had a calendar in my QV document that was structured :

month_no_id     week_no_id     year     monthname     day     calendar_date                 week     month     quarter

201012               201052          2010     Dec               30       30/12/2010 00:00:00      52               12     1

and the formula

=sum({$<[calendar_date]={"$(=Only([calendar_date]))"}>} [gross] )

worked to give the sales on the selected day

I added    Floor(calandar_date) tp my load script which added:

DateNum

40907

to the calendar, and now the above formula won't work.

I don't understand why - does anyone know why this is?

Much appreciated

[avinashelite]

Yes, The format is different that's why it won't work

would you please share the app

[#3]

Thanks Avinash,

The calendar is below.

I read here QlikView Addict: Dates in QlikView - Part 1 that QlikView will use the numberic value first, in this case the dateNum.

I changed the formula to this:

=Sum({<year=, month=, quarter=, week=, calendar_date=, month_no_id=, day=,week_no_id=,  dateNum={">=$(=Num(WeekStart(Max(dateNum))))<=$(=Max(dateNum))"}>} gross_sales)

but it will not work.

[vinieme12]

Firstly , If you just want to show data for selected day you don't event need set analysis

The next thing is you are trying to compare a timestamp with an Integer

You either compare date with date or an integer with integer

[vinieme12]

You need to encapsulate the number in date () function to tell qlikview that the number is a date and then you use weekstart () on the date

=Sum({<year=, month=, quarter=, week=, calendar_date=, month_no_id=, day=,week_no_id=,  dateNum={">=$(=Num(WeekStart(date (Max(dateNum)))))<=$(=Max(dateNum))"}>} gross_sales)

[#6]

Thanks very much Vineeth, That is very clear. I understand now.

[#7]

Thanks very much Vineeth.

Kind regards,

Stefan