Date format

Wed, 27 Feb 2013 07:57:23 GMT

My Financial year runs from Sep - Aug and I have the following in my load script (results in UI in red with FinYear 2012 - 2013 selected:

LET vStartMonthFinYear = 9;

Load

Date(DocDate) as DocDate, (eg 2012/09/03)
Month(DocDate)&' '&Year(DocDate) as Period, eg. Sep 2012

Month(DocDate) as CalenderMonth, eg Sep

Year(DocDate) as CalenderYear, eg.2012
Day(DocDate) as CalenderDay, 3

YearName(DocDate,0,$(vStartMonthFinYear)) as FinYear, (result = 2011 - 2012 and 2012 - 2013)
YearName(DocDate,-1,$(vStartMonthFinYear)) as PrevFinYear, (result = 2010 - 2011 and 2011 - 2012)
YearStart(DocDate,0,$(vStartMonthFinYear)) as FinYearStart, (2012/09/01)
YearStart(DocDate,-1,$(vStartMonthFinYear)) as PrevFinYearStart, (2011/09/01)
YearEnd(DocDate,0,$(vStartMonthFinYear)) as FinYearEnd, (2013/08/31)

YearEnd(DocDate,-1,$(vStartMonthFinYear)) as PrevFinYearEnd, (2012/08/31)

My problem is the following in the UI - with FinYear 2012 - 2013 selected

Num(DocDate) = eg. 40787

Num(PrevFinYearStart) = 40787

but

Num(PrevFinYearEnd) = 41152.999999988

Question: Why does it have decimals (I suppose it is a timestamp) and how can I get rid of it.

I would like to have a set analysis for prev fin year similar to

Sum({<DocDate={">=PrevFinYearStart<=PrevFinYearEnd"}>}Turnover) but no matter how I fiddle around with it, it doesn't work and I suspect those decimals are the culprit.

Thank you

Danie

Re: Date format

lironbaram — Wed, 27 Feb 2013 08:48:04 GMT

just use

date(floor(YearEnd(DocDate,0,$(vStartMonthFinYear)) ))as FinYearEnd, (2013/08/31)

date(floor(YearEnd(DocDate,-1,$(vStartMonthFinYear)))) as PrevFinYearEnd, (2012/08/31)

topic Date format in QlikView

Date format

Re: Date format