Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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. :-)

Tags (1)
28 Replies
sokkorn_cheav
Honored Contributor

Re: Last year to "todays" date

Hi,

Sound you like to calculate YTD. Is it correct? BTW, UCYEA4 is a date field (24/05/13)? Do you have any calendar table?

Here is some clue for YTD

Sum({<DateField = {">=$(=YearStart(DateField,-1))<=$(=AddMonths(Max(DateField),-12))"} >} Sales)

Regards,

Sokkorn

Not applicable

Re: Last year to "todays" date

Sorry UCYEA4 = 2013 and yes, I have a calendar table.

I  want to calculate Last YTD, ie the purpose is to compare sales

1/1/12-24/5/12 to 1/1/13-24/5/13 using selection field ucyea4 (2013) and calendarmonthname (jan-may)

sokkorn_cheav
Honored Contributor

Re: Last year to "todays" date

Hi,

Can you replace [DateField] to your date field in my sample set analysis and see the resule?

Regards,

Sokkorn

er_mohit
Honored Contributor II

Re: Last year to "todays" date

Try this

sum({$<Date = {">=$(=MonthStart(Max(Date),-12))<=$(=MonthEnd(Max(Date)))"},Year=,Month=>}Sale)

Not applicable

Re: Last year to "todays" date

Sorry did not work as my field is not a DATE it is a YEAR.

The user has 2 selections boxes; Year and Month

Field for Year is UCYEA4 and represents 2011, 2012, 2013 etc

Field for Month is CalendarMonthName and represents Jan, Feb, Mars, Apr, May etc through to Dec.

If I select  2013 May I expect to see a sum of sales for

* This Year to Date: Between 01/05/13 and 27/05/13 (i.e. the date and time it was last reloaded)

* Last Year to Date: Between 01/05/12 and 27/05/12 (i.e. same date and time as this year but for last year)

However, I can only manage to get Last Year to Date to sum the whole month for last year, i.e. 01/05/12 - 31/04/12 and that's not what I want.

Maybe I need to change the "format" for year and calendarmonthname..

Not applicable

Re: Last year to "todays" date

Sorry, did not work. I am not using a DATE field only YEAR (2012, 2013 etc) and CalendarMonthName (Jan, Feb etc)

How do I convert these 2 into 1 date field which stops at Max(date)?

Highlighted
sokkorn_cheav
Honored Contributor

Re: Last year to "todays" date

Hi khagglund,

Do you have date field represent data like 27/05/13? We should use date to check condition while your target is YTD and LYTD.

Regards,

Sokkorn

Not applicable

Re: Last year to "todays" date

The transactions contain a year field (2012) and a date filed (e.g. 20120527). Does not contain a month.

I have in a calendar table created Apr, May etc from the date field above.

The users do not want to select a date they want to select a Year and a Month. (2012, May etc) 

Is this to hard?

sokkorn_cheav
Honored Contributor

Re: Last year to "todays" date

Hi khagglund,

Sure, use no need to select date (20120527). In your case, we need to calculate YTD. And to get this job done, we need one hidden field for date (20130527). How many column in your calendar table?

Regards,

Sokkorn