topic Adding Datenum to calendar invalidates all formulas? in QlikView

Adding Datenum to calendar invalidates all formulas?

Fri, 07 Apr 2017 15:25:16 GMT

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

Re: Adding Datenum to calendar invalidates all formulas?

avinashelite — Fri, 07 Apr 2017 15:27:53 GMT

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

would you please share the app

Re: Adding Datenum to calendar invalidates all formulas?

Fri, 07 Apr 2017 16:12:09 GMT

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.

Re: Adding Datenum to calendar invalidates all formulas?

vinieme12 — Fri, 07 Apr 2017 16:36:43 GMT

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

Re: Adding Datenum to calendar invalidates all formulas?

vinieme12 — Fri, 07 Apr 2017 16:38:29 GMT

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)

Re: Adding Datenum to calendar invalidates all formulas?

Mon, 10 Apr 2017 07:12:53 GMT

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

Re: Adding Datenum to calendar invalidates all formulas?

Mon, 10 Apr 2017 07:17:33 GMT

Thanks very much Vineeth.

Kind regards,

Stefan