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
Sokkorn
Master
Master

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:

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

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

By the way, can you share your app?

Regards,

Sokkorn

Not applicable
Author

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

Not applicable
Author

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.

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,

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

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

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

See attached sample file.

Regards,

Sokkorn

Not applicable
Author

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?

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,

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

Sum({$<[CalendarDateTYToday] = {">=$(=Floor(MonthStart(AddYears(Max([CalendarDateTYToday]),-1))))<=$(=Floor(AddYears(ReloadTime(),-1)))"},

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

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

Untitled.png

Apply fomular above into your app and check the result.

Regards,

Sokkorn

Not applicable
Author

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

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,

[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

Not applicable
Author

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