topic Re: Compare YTD Day vs LYTD Day in QlikView

Compare YTD Day vs LYTD Day

Wed, 19 Oct 2011 16:30:18 GMT

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;

Compare YTD Day vs LYTD Day

Wed, 19 Oct 2011 18:22:41 GMT

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.

Re: Compare YTD Day vs LYTD Day

Miguel_Angel_Baeyens — Wed, 19 Oct 2011 18:28:29 GMT

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

Re: Compare YTD Day vs LYTD Day

Wed, 19 Oct 2011 18:44:08 GMT

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

Re: Compare YTD Day vs LYTD Day

Miguel_Angel_Baeyens — Wed, 19 Oct 2011 18:52:28 GMT

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)

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

Re: Compare YTD Day vs LYTD Day

Thu, 20 Oct 2011 14:54:25 GMT

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

Re: Compare YTD Day vs LYTD Day

Miguel_Angel_Baeyens — Thu, 20 Oct 2011 17:18:12 GMT

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.

Re: Compare YTD Day vs LYTD Day

Thu, 20 Oct 2011 17:36:18 GMT

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.

Re: Compare YTD Day vs LYTD Day

Miguel_Angel_Baeyens — Thu, 20 Oct 2011 17:59:03 GMT

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

Re: Compare YTD Day vs LYTD Day

Mon, 24 Oct 2011 16:39:06 GMT

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)

Re: Compare YTD Day vs LYTD Day

Miguel_Angel_Baeyens — Mon, 24 Oct 2011 17:37:11 GMT

Hi Alex,

If you mean the whole aggregation yes, there should do something like the following

LET vDate1 = Date(Today() -1); // this is the example of the date -1 value
Table:
LOAD Sum(Cnt) AS Expression
FROM Source
WHERE Date = '$(vDate1)'

Of course you can check every record Date value to see when that condition returns true, but I think the above will work best, since Date will be the same for all records (set analysis is calculated once and for the whole set of records).

Or if you already have the calendar flags, use them in the If() so when the date is today, or the most updated, and the day -1 flag is true, then sum.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Compare YTD Day vs LYTD Day

rajni_batra — Thu, 03 May 2012 11:42:30 GMT

Hello,i m having a problem in expressionsum({$<[CAL.Fiscal Year] = {$(MaxFiscalYearGL),$(=Max([CAL.FiscalYear])-1)}, [CAL.Fiscal Month] ={"=$(=max([CAL.FiscalMonth]))"}>}Amount)*-1/Denominatorits giving me data FOR YTD till mar but FOR LYTD only for April monthi want from both to return for the same month like \Apr 12 and Apr11

please help

Compare YTD Day vs LYTD Day

Thu, 10 May 2012 20:50:19 GMT

Miguel, this function, does not give me last years day, just date. Thats where I'm getting hung up.

: If(date($(vMinDate) + rowno() - 1) = AddYears(Today(), -1), 1, 0)) AS TodayLYFlag,