Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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. 🙂
OK
I have plenty of columns but nothing for "todaysdate". I do have a variable called vTodaysDate (2013-05-27)
Hi,
In your calendar table, do you have date field (like 2013-05-27)?
Rgds,
Sokkorn
Yes, it's called CalendarDate but not connect 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
OK, I’ll try that.
Thank You.
Katarina Hagglund
Senior Business Analyst
Stuart Alexander & Co Pty. Ltd.
E khagglund@stalex.com.au<mailto:khagglund@stalex.com.au>
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/>
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
Senior Business Analyst
Stuart Alexander & Co Pty. Ltd.
E khagglund@stalex.com.au<mailto:khagglund@stalex.com.au>
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/>
Hi Katarina,
Try this for last year
Sum({$<[CalendarDateTYToday] = {">=$(=Floor(YearStart(Max(CalendarDateTYToday),-1)))<=$(=Floor(Today(),-1))"}>} amount)
Regards,
Sokkorn
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
Senior Business Analyst
Stuart Alexander & Co Pty. Ltd.
E khagglund@stalex.com.au<mailto:khagglund@stalex.com.au>
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/>
Hi Katarina,
What about this one:
Sum({$<[CalendarDateTYToday] = {">=$(=Floor(YearStart(Max(CalendarDateTYToday),-1)))<=$(=Floor(AddYears(Today(),-1)))"}>} amount)
Regards,
Sokkorn
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.
Sum({$<[CalendarDateLYToday] = {">=$(=Floor(YearStart(Max(CalendarDateLYToday))))<=$(=Floor(AddYears(Today(),-1)))"}>} amount)
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?
Katarina Hagglund
Senior Business Analyst
Stuart Alexander & Co Pty. Ltd.
E khagglund@stalex.com.au<mailto:khagglund@stalex.com.au>
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/>