Date Field

[evansabres]

I have a field called 'Created' which is a date and time field, for example 6/9/16 3:09 PM

My data centers around the submissions of projects through a ticket system. So, for every ticket submitted there is a Created time stamp. I am attempting to chart out the submission by month and year. How can I manipulate this field to do that? In other words, my problem currently is that when I attempt to create a chart, each of the tickets are their own data point because they all have unique time stamps.

[johnw]

First, extract a [Created Date] from that field in script:

daystart([Created]) as [Created Date],

Then create a master calendar on [Created Date] that has a [Created Month] and [Created Year]. Now you can use these fields as dimensions and count tickets by those dimensions.

[evansabres]

John, thank you, how do you create a master calendar?

[johnw · Accepted solution]

I'm sure there are a number of guides, but once you've loaded in your main data, this might do the trick:

Calendar:
LOAD *
,date(monthstart([Created Date]),'MMM YYYY') as [Created Month]
,date(yearstart([Created Date]),'YYYY') as [Created Year]
;
LOAD date(fieldvalue('Created Date',recno())) as [Created Date]
AUTOGENERATE fieldvaluecount('Created Date')
;

[sunny_talwar]

May be look here:

Better Calendar Scripts | Qlikview Cookbook

[johnw]

OK, my script assumed we didn't need any dates in the calendar that aren't in the data, which is often the case. If not, you can go from min to max. Untested, but:

MinMax:
LOAD
min([Created Date]) as Min
,max([Created Date]) as Max
;
LOAD date(fieldvalue('Created Date',recno())) as [Created Date]
AUTOGENERATE fieldvaluecount('Created Date')
;
LET vMin = peek('Min');
LET vMax = peek('Max');
DROP TABLE MinMax;

Calendar:
LOAD *
,date(monthstart([Created Date]),'MMM YYYY') as [Created Month]
,date(yearstart([Created Date]),'YYYY') as [Created Year]
;
LOAD $(vMin)+recno()-1 as [Created Date]
AUTOGENERATE $(vMax)-$(vMin)+1
;

I see that Rob was saying that peek() is dangerous, but I just read the QVC script for the same function, and it uses peek. So... I guess he was just saying it was easy to typo something, and if you use QVC it's been carefully tested and doesn't have that sort of typo in that piece of script.

Honestly, why I haven't started using QVC, I have no idea. It's such a good idea, and it's probably been mature and reliable for a long time. So yeah, there's one answer. "Want to create a master calendar? Get QlikView Components!".

[johnw]

Wait, why do we even bother with min and max variables? What's wrong with this?

Calendar:
LOAD *
,date(monthstart([Created Date]),'MMM YYYY') as [Created Month]
,date(yearstart([Created Date]),'YYYY') as [Created Year]
;
LOAD date(fieldvalue('Created Date',recno()))+iterno()-1 as [Created Date]
AUTOGENERATE fieldvaluecount('Created Date')
WHILE recno() < fieldvaluecount('Created Date')
  AND date(fieldvalue('Created Date',recno()))+iterno()-1
    < date(fieldvalue('Created Date',recno()+1))
;

Seems like it would work with no variables required. Or we could do it by first building the dates we have, then filling in the blanks. Might be easier to read, even if slightly longer. I need to do some testing. Also searching the forum.

Edit: Because fieldvalue() doesn't return in numeric sequence...

[johnw]

Ah, here we go. Use Henric's approach, but with fieldvalue() instead of a resident load, and we get something nice and short, no variables, and it should be fast. I'm sure I'm only the latest person to reinvent this wheel, but no more variables and peek() for me.

Calendar:

LOAD *

,date(monthstart(Date),'MMM YYYY') as Month

,date(yearstart(Date),'YYYY') as Year

;

LOAD  Min+iterno()-1 as Date

WHILE Min+iterno()-1 <= Max

;

LOAD min(fieldvalue('Date',recno())) as Min

,    max(fieldvalue('Date',recno())) as Max

AUTOGENERATE fieldvaluecount('Date')

;

The Master Calendar

[johnw]

Ahhhhh, looks like this is the method used by seebach in:

The Fastest Dynamic Calendar Script (Ever)

Well, if I'm going to reinvent the wheel, the fastest wheel (ever) is a good one to reinvent.