Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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)

sokkorn_cheav
Honored Contributor

Re: Last year to "todays" date

Hi,

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

Rgds,

Sokkorn

Not applicable

Re: Last year to "todays" date

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

sokkorn_cheav
Honored Contributor

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

Highlighted
Not applicable

Re: Last year to "todays" date

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

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

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_cheav
Honored Contributor

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

Not applicable

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

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_cheav
Honored Contributor

Re: Last year to "todays" date

Hi Katarina,

What about this one:

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

Regards,

Sokkorn

Not applicable

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.

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