Quarter to Date, Prior Quarter to Date in Calendar

[#1]

Attached is the date portion of my load script for creating month, quarter, yearquarter, year and yearmonth.  I've been told that we will now need current quarter to date and prior quarter to date for our graphs.  Can someone help me with the QTD and prior QTD code to add to my script?  I'm new to Qlikview so please keep it simple.  Thank you!

MONTH(DATE#([Policy Inception Date],'DD-MMM-YY')) as DiscMonth,
CEIL(MONTH(DATE#([Policy Inception Date],'DD-MMM-YY'))/3) as DiscQuarter,
YEAR(DATE#([Policy Inception Date],'DD-MMM-YY'))&CEIL(MONTH(DATE#([Policy Inception Date],'DD-MMM-YY'))/3) as DiscYearQuarter,
YEAR(DATE#([Policy Inception Date],'DD-MMM-YY')) as DiscYear,
YEAR(DATE#([Policy Inception Date],'DD-MMM-YY')) &'_'&MONTH(DATE#([Policy Inception Date],'DD-MMM-YY')) as DiscYearMonth,

[swuehl]

If you want to add a flag to your script, it could look like

-1 * InQuarterToDate(DATE#([Policy Inception Date],'DD-MMM-YY'), today(), 0 ) as IsQTD,

-1 * InQuarterToDate(DATE#([Policy Inception Date],'DD-MMM-YY'), today(), -1 ) as IsPrevQTD,

In any expression, you could use something like sum(IsQTD * Value) to sum only the values linked to dates within this QTD.

Alternatively, instead of using flags in the script, you could do the QTD etc. calculation in the front end, using set analysis. If you are interested in this (it looks difficult at first glance, but it's not, if you get used to it and it is quite powerful), please have a look in the Help or manual and search the forum. I also found this quite useful:

http://iqlik.wordpress.com/2010/09/11/the-magic-of-set-analysis-syntax-and-examples/

http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

Hope this helps,

Stefan

[#3]

Thank you for the help. The two links were particularly helpful. I have the QTD working correctly now.

Susan Skirpan

Data Analyst

Markel Corporation

Information Management

4521 Highwoods Parkway, Glen Allen, VA 23059

Direct: (804) 525-7002

www.markelcorp.com <http://www.markelcorp.com/