Compare YTD Day vs LYTD Day

[#1]

I'm having trouble with this concept and how I should go about it. How would I compare the following dates?

With set analysis or within my date script?

9/30/2010: Thursday – Last Year

9/29/2011: Thursday – Current Year

This is what I'm using to create me dates.

TEMP:

LOAD

          num(min(link_Date)) AS MinDate,

          num(max(link_Date)) AS MaxDate

RESIDENT

          Checks;

LET vMinDate = peek('MinDate', 0, 'TEMP');

LET vMaxDate = peek('MaxDate', 0, 'TEMP');

DROP TABLE TEMP;

DateIsland:

LOAD

          date($(vMinDate) + rowno() - 1) AS D,

          year($(vMinDate) + rowno() - 1) AS Y,

          month($(vMinDate) + rowno() - 1) AS M,

          date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MY

AUTOGENERATE vMaxDate - vMinDate + 1;

[#2]

Hi Alex,

I am not sure if I understood you but the following might be helpful:

To "DateIsland" table add the below row:

31*(month($(vMinDate) + rowno() - 1)-1)+Day($(vMinDate) + rowno() - 1) as RunningDay

Create the following variables:

vMaxYear = max(Y)

vYTD = Max({<Y={"$(=vMaxYear)"}>} RunningDay) //Current Year

vLYTD = Max({<Y={"$(=vMaxYear-1)"},RunningDay={"<=$(=vYTD)"}>} RunningDay) //Last Year

--

Kiran.

[Miguel_Angel_Baeyens · Accepted solution]

Hello Alex,

Check the suggestion I gave you in the other post in regards to set analysis. In the script you can easily create a flag field for the date of today and today one year ago

DateIsland:
LOAD
          date($(vMinDate) + rowno() - 1) AS D,
          If(date($(vMinDate) + rowno() - 1) = Today(), 1, 0) AS TodayCYFlag,
          If(date($(vMinDate) + rowno() - 1) = AddYears(Today(), -1), 1, 0)) AS TodayLYFlag,
          year($(vMinDate) + rowno() - 1) AS Y,
          month($(vMinDate) + rowno() - 1) AS M,
          date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
AUTOGENERATE vMaxDate - vMinDate + 1;

Then you can use two simple expressions for today

Sum({< TodayCYFlag = {1} >} Sales)

and last year

Sum({< TodayLYFlag = {1} >} Sales)

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#4]

Nice, this fits my need exactly!!

Now, the only thing is that it breaks when I click on any of my date dimensions. I would like to have a table that rolls up all of say Januarys YTD with the adjusted Date and compare it to LY's Total. But this is a great start.

Would I just create an Adjusted CY Date in my date Island?

Alex

[Miguel_Angel_Baeyens]

Hi Alex,

I'm glad to help. In regards to the rolling month, check the InYearToDate() and YearToDate() functions, that will return true if a given date fits into a given range (kind of) so you can create some flag fields for the different months. There are some examples I used in this thread and in this application.

If you want those expressions to keep their values regardless the selections made in the document, you can use this instead (check he "1" before the "<" in the set analysis)

Sum({1< TodayCYFlag = {1} >} Sales)

or

Sum({< DateField = , TodayCYFlag = {1} >} Sales)

nothing in the right part after the date field on purpose to ignore selections only in this field.

Hope that helps on building your calendar and set analysis expressions.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#6]

Thanks Miguel. I really appreciate the help. So I've got most of the flags created, but I'm still a little confused.

How am I supposed to adjust this years date to line up with last years date? Its just not sinking in yet....

[Miguel_Angel_Baeyens]

Hi Alex,

If you check the application above, you will see how you will have a chart, say a straight table, with year as dimension and something like the expression above one for each year you want to show. In you case, is your field dimension is YearMonth because you want to show the difference between this year and last year, expressions should be very similar, since you want to sum up all values in the Sum() field with current year flag set to 1 (current month, etc) and last year set to 1 as well.

Maybe with your actual structure we can figure it out better uploading a sample application or so.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

EDIT: Added very simple application with ID, Date and Amount of sales with charts for the whole year and YTD.

[#8]

Here is a txt file of the data I'm using. So I can get a good view for month, but I'm still not comparing 1/1/2011 to 1/2/2010.

[Miguel_Angel_Baeyens]

Hi,

Using the very same file I uploaded above with random data, here you are the application with your data showing both years in a bar chart and a pivot table chart.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#10]

Hey Miguel,

  I'm revisiting the issue. Is there a way to put this set analysis expression into the script?

=sum({<Date={'$(=date(only(Date)-1))'}>} Cnt)