Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

sokkorn_cheav
Honored Contributor

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:

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

Highlighted
Not applicable

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

Not applicable

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.

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,

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

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?

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,

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

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

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,

[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

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