topic Offsetting Dates in QlikView

Offsetting Dates

Fri, 18 Jun 2010 19:08:54 GMT

I am trying to do a comparison of this year sales to last year sales, YTD,

My data however gets generated over night to include up to the previous days sales.

So if comparing to last year to date, I have to offset the results by one day. I came up with this expression but I am convinced it is not right,

sum(if(yeartodate([INVC DATE]-1,-1), [TOT SALES])

What do you think?

fernandotoledo — Fri, 18 Jun 2010 19:28:53 GMT

Yeah, it´s not ritght : )

This expression offset is set in years, so its returning the ytd sales for the last year!

You can make calculations with dates like integers:

sum(if(inYearToDate(Sales_Date, (today()-1) ,0), Value)

For expressions that needs an offset of weeks, months there is a group of functions (addmonth(), addyear(),etc) that does the job!

Hope it helps!

Fernando

Fri, 18 Jun 2010 19:35:15 GMT

I was trying to get YTD for the previous year, but I want YTD of last year less one day.

If i were to go into my document today, my YTD data is up to June 17, 2010 .

I want my expression to give me comparison of YTD up to June 17, 2009

fernandotoledo — Fri, 18 Jun 2010 19:40:04 GMT

Then you can combine the expressions:

Today()-1 => Previous day

inYearToDate(...,..., -1) => Previous Year

Fri, 18 Jun 2010 19:49:58 GMT

ok and to take it one step further, if i want month to date of last year less one day, what should my expression look like?

fernandotoledo — Fri, 18 Jun 2010 20:03:12 GMT

You have a bunch of Date & Time functions! In this case it would be the InMonthToDate() wich works like the year to date one.

It´s worth to look for 'Date and time' in the help to see them all!

best regards

Fernando