28 Replies Latest reply: Jun 5, 2013 3:17 AM by khagglund

# Last year to "todays" date

I have a column called Gross Sales Values - Last Year To Date

My selection is:

Year: 2013.

Month: jan, feb, mar, apr, may (i.e I can select 1 or many months but in this ex I have selected all from jan to may)

If I make above selection today  (24/05/13) I would like to see the sales between 01/01/12 and24/05/12 but I cannot figure out how to write the set analysis statement.

e.g.

sum({\$<UCYEA4 = {\$(=Year(MakeDate(UCYEA4)))}>} amount) => will today (24/05/13) give me sales between 01/01/13 and 24/05/13

sum({\$<UCYEA4 = {\$(=Year(MakeDate(UCYEA4)-1))}>} amount) => will today (24/05/13) give me sales between 01/01/12 and 31/05/12 because last year may has already been a full month.

I assume I need to add a "todaysdate" somehow using the "calendermonthname" (format "jan") ?

Can you help me please. :-)

• ###### Re: Last year to "todays" date

Hi,

Sound you like to calculate YTD. Is it correct? BTW, UCYEA4 is a date field (24/05/13)? Do you have any calendar table?

Here is some clue for YTD

Regards,

Sokkorn

• ###### Re: Last year to "todays" date

Sorry UCYEA4 = 2013 and yes, I have a calendar table.

I  want to calculate Last YTD, ie the purpose is to compare sales

1/1/12-24/5/12 to 1/1/13-24/5/13 using selection field ucyea4 (2013) and calendarmonthname (jan-may)

• ###### Re: Last year to "todays" date

Hi,

Can you replace [DateField] to your date field in my sample set analysis and see the resule?

Regards,

Sokkorn

• ###### Re: Last year to "todays" date

Sorry did not work as my field is not a DATE it is a YEAR.

The user has 2 selections boxes; Year and Month

Field for Year is UCYEA4 and represents 2011, 2012, 2013 etc

Field for Month is CalendarMonthName and represents Jan, Feb, Mars, Apr, May etc through to Dec.

If I select  2013 May I expect to see a sum of sales for

* This Year to Date: Between 01/05/13 and 27/05/13 (i.e. the date and time it was last reloaded)

* Last Year to Date: Between 01/05/12 and 27/05/12 (i.e. same date and time as this year but for last year)

However, I can only manage to get Last Year to Date to sum the whole month for last year, i.e. 01/05/12 - 31/04/12 and that's not what I want.

Maybe I need to change the "format" for year and calendarmonthname..

• ###### Re: Last year to "todays" date

Hi khagglund,

Do you have date field represent data like 27/05/13? We should use date to check condition while your target is YTD and LYTD.

Regards,

Sokkorn

• ###### Re: Last year to "todays" date

The transactions contain a year field (2012) and a date filed (e.g. 20120527). Does not contain a month.

I have in a calendar table created Apr, May etc from the date field above.

The users do not want to select a date they want to select a Year and a Month. (2012, May etc)

Is this to hard?

• ###### Re: Last year to "todays" date

Hi khagglund,

Sure, use no need to select date (20120527). In your case, we need to calculate YTD. And to get this job done, we need one hidden field for date (20130527). How many column in your calendar table?

Regards,

Sokkorn

• ###### Re: Last year to "todays" date

OK

I have plenty of columns but nothing for "todaysdate". I do have a variable called vTodaysDate (2013-05-27)

• ###### Re: Last year to "todays" date

Hi,

In your calendar table, do you have date field (like 2013-05-27)?

Rgds,

Sokkorn

• ###### Re: Last year to "todays" date

Yes, it's called CalendarDate but not connect to todays date.

• ###### Re: Last year to "todays" date

Hi khagglund,

I suggest you create one more field for calculation. Like

Floor(CalendarDate)    AS [_CalDate],

...

And use [_CalDate] in set analysis below:

Sum({\$<[_CalDate] = {">=\$(=Floor(YearStart(Max([_CalDate]))))<=\$(=Floor(Today()))"}>} Sales)

Try it and let me know.

Regards,

Sokkorn

• ###### Re: Last year to "todays" date

OK, I’ll try that.

Thank You.

Katarina Hagglund

Stuart Alexander & Co Pty. Ltd.

Address | Level 3, 1 Smail Street | Ultimo | NSW 2007 | Australia

Mail | P.O. Box 950 | Ultimo | NSW 2007| Australia

P 02 9282 7714 | F 02 9282 7714 | M 0437 409 442

www.stuartalexander.com.au<http://www.stuartalexander.com.au/>

• ###### Re: Last year to "todays" date

I think this might work pretty good. However it’s showing this year, i.e. to 27/05/13

What do I need to add below to show last year, i.e. 27/05/12?  -352 somewhere or just – 1 ?

Sum({\$<[CalendarDateTYToday] = {">=\$(=Floor(YearStart(Max(CalendarDateTYToday))))<=\$(=Floor(Today()))"}>} amount)

>=  20130101 <= 20130527

Regards,

Katarina Hagglund

Stuart Alexander & Co Pty. Ltd.

Address | Level 3, 1 Smail Street | Ultimo | NSW 2007 | Australia

Mail | P.O. Box 950 | Ultimo | NSW 2007| Australia

P 02 9282 7714 | F 02 9282 7714 | M 0437 409 442

www.stuartalexander.com.au<http://www.stuartalexander.com.au/>

• ###### Re: Last year to "todays" date

Hi Katarina,

Try this for last year

Sum({\$<[CalendarDateTYToday] = {">=\$(=Floor(YearStart(Max(CalendarDateTYToday),-1)))<=\$(=Floor(Today(),-1))"}>} amount)

Regards,

Sokkorn

• ###### Re: Last year to "todays" date

No unfortunately it did not work. It’s still gives me the same values in This Year and Last Year, refer below.

The figure is for 20130501 to 20130527. They should not be the same.

Katarina Hagglund

Stuart Alexander & Co Pty. Ltd.

Address | Level 3, 1 Smail Street | Ultimo | NSW 2007 | Australia

Mail | P.O. Box 950 | Ultimo | NSW 2007| Australia

P 02 9282 7714 | F 02 9282 7714 | M 0437 409 442

www.stuartalexander.com.au<http://www.stuartalexander.com.au/>

• ###### Re: Last year to "todays" date

Hi Katarina,

Regards,

Sokkorn

• ###### Re: Last year to "todays" date

Nope. The result is 0 in the LYTD colum

I creted a new field called CalendarDateLYToday and try below but it now shows YTD 27/05/13 in both columns again.

CalendarDateTYtoday = 41421

CalendarDateLYtoday = 41069

Sorry for all the trouble but I’m fairly new to QV and I’m not a programmer!

Maybe I have to start all over with this dates again?

cid:image001.png@01CE5AEA.060DD520

Katarina Hagglund

Stuart Alexander & Co Pty. Ltd.

Address | Level 3, 1 Smail Street | Ultimo | NSW 2007 | Australia

Mail | P.O. Box 950 | Ultimo | NSW 2007| Australia

P 02 9282 7714 | F 02 9282 7714 | M 0437 409 442

www.stuartalexander.com.au<http://www.stuartalexander.com.au/>

• ###### Re: Last year to "todays" date

Hi Katarina,

Your calendar table is good. Just add only one field [CalendarDateTYToday] is ok.

Now take a look this one and let me know the result

YTD:

Sum({\$<[CalendarDateTYToday] = {">=\$(=Floor(YearStart(Max([CalendarDateTYToday]))))<=\$(=Max([CalendarDateTYToday]))"}>} amount)

LYTD:

YearField= , QuarterField= , MonthField=, WeekField= >} amount)

By the way, can you share your app?

Regards,

Sokkorn

• ###### Re: Last year to "todays" date

Hi,

It is after hours here in Australiaso I will address this email tomorrow

Thank you for all your help today!

Set Analysis is a bit frustrating.

Katarina Hagglund

• ###### Re: Last year to "todays" date

Hi, I’m been off sick so have not been able to look at this until now. I tried your LYTD below but it still does not work.

Column “GSV LYTD”  will now show GSV (gross sales value) from 01/01/12 to 31/05/12.  I want it to show GSV for 01/05/12 to 27/05/12 as my latest reload was 27/05/13.

I’ve attached a cut down version for my app.

cid:image001.png@01CE5D2D.3BF0EE80

Katarina Hagglund

Stuart Alexander & Co Pty. Ltd.

Address | Level 3, 1 Smail Street | Ultimo | NSW 2007 | Australia

Mail | P.O. Box 950 | Ultimo | NSW 2007| Australia

P 02 9282 7714 | F 02 9282 7714 | M 0437 409 442

www.stuartalexander.com.au<http://www.stuartalexander.com.au/>

• ###### Re: Last year to "todays" date

Hi Katarina,

Sound you want to calcualte last year month to date. Let try

UCYEA4= , CalendarQuarter= , CalendarMonthName=, CalendarWeekOfYear= >} amount)

See attached sample file.

Regards,

Sokkorn

• ###### Re: Last year to "todays" date

Getting closer but still not there.

Last reload was 30/05/13 at 1:52:37pm  so if I select May 2013 I want to compare the sum of:

01/05/12 – 30/05/12   to    01/05/13 - 30/05/13.

The value I’m getting in GSV LYTD is to the 31/05/12 and that’s 1 day to much.

I would like the GSV LYTD to show 477, 594 not 483, 347.

It’s as if it’s ignoring the actual date in the month and just going to end of month?

cid:image001.png@01CE5D3E.5A9EB8B0

cid:image003.png@01CE5D3F.A158FE90

Katarina Hagglund

Stuart Alexander & Co Pty. Ltd.

Address | Level 3, 1 Smail Street | Ultimo | NSW 2007 | Australia

Mail | P.O. Box 950 | Ultimo | NSW 2007| Australia

P 02 9282 7714 | F 02 9282 7714 | M 0437 409 442

www.stuartalexander.com.au<http://www.stuartalexander.com.au/>

• ###### Re: Last year to "todays" date

Hi Katarina,

If you need to calculate data base on last reload time then we can use ReloadTime() function in our set analysis. Here

UCYEA4= , CalendarQuarter= , CalendarMonthName=, CalendarWeekOfYear= >} amount)

Anyhow, I got the same result because of last reload time

Apply fomular above into your app and check the result.

Regards,

Sokkorn

• ###### Re: Last year to "todays" date

No, made no difference, stayed the same. Instead it calculated Apr incorrectly if I selected Apr  instead of May.

Katarina Hagglund

Stuart Alexander & Co Pty. Ltd.

Address | Level 3, 1 Smail Street | Ultimo | NSW 2007 | Australia

Mail | P.O. Box 950 | Ultimo | NSW 2007| Australia

P 02 9282 7714 | F 02 9282 7714 | M 0437 409 442

www.stuartalexander.com.au<http://www.stuartalexander.com.au/>

• ###### Re: Last year to "todays" date

Hi Katarina,

[SALESTATS] link to [Calendar] via [CalendarMonthNo] and [CalendarYear]. Do not use this data model. And this is a problem while we use [CalendarDateTYToday] in set analysis.

We should link between [SALESTATE] and [Calendar] via [SALESTATE].[UCIVDT] = [Calendar].[CalendarDate] (make sure it same format) then try your set analysis again.

Regards,

Sokkorn

• ###### Re: Last year to "todays" date

I’ve rewritten the report a bit but I still have not managed to get this to work, refer attached qvw file.

Regards,

Katarina Hagglund

• ###### Re: Last year to "todays" date

Try this

sum({\$<Date = {">=\$(=MonthStart(Max(Date),-12))<=\$(=MonthEnd(Max(Date)))"},Year=,Month=>}Sale)

• ###### Re: Last year to "todays" date

Sorry, did not work. I am not using a DATE field only YEAR (2012, 2013 etc) and CalendarMonthName (Jan, Feb etc)

How do I convert these 2 into 1 date field which stops at Max(date)?