Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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. 🙂

28 Replies
Not applicable
Author

OK

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

Sokkorn
Master
Master

Hi,

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

Rgds,

Sokkorn

Not applicable
Author

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

Sokkorn
Master
Master

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

Not applicable
Author

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/>

Not applicable
Author

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/>

Sokkorn
Master
Master

Hi Katarina,

Try this for last year

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

Regards,

Sokkorn

Not applicable
Author

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/>

Sokkorn
Master
Master

Hi Katarina,

What about this one:

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

Regards,

Sokkorn

Not applicable
Author

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/>