topic Re: Comparing This Year Date's Value with Last Year Same Date Value in QlikView

Comparing This Year Date's Value with Last Year Same Date Value

Fri, 23 Jan 2015 04:07:35 GMT

I have a table of 3 years of data. Assume Columns are Date_Key, Value_For_Date, Month_of_Date, Year_Of_Date.

When user selects Year_of_Date, Month_Of_Date I need to show a table in the following format

Date Month_Value Last_Year_Dt Last_Year_Value

02/01/2015 1000 02/02/2014 500

02/02/2015 800 02/03/2014 650

02/03/2015 750 02/04/2014 825

02/28/2015 1000 03/01/2014 750

Is it possible to get Last_Year_Value with just formula in the expression.

Would appreciate your help.

Thanks

Raghu

Re: Comparing This Year Date's Value with Last Year Same Date Value

Fri, 23 Jan 2015 04:15:37 GMT

Hi,

Can you attach the sample file.

so that we can help you in better way.

we are unable to open the qvw file...

@Sub2u

Re: Comparing This Year Date's Value with Last Year Same Date Value

Fri, 23 Jan 2015 05:03:28 GMT

Here is the script and the Chart Object Dimensions/Expressions. Thanks for looking at this.

Raghu

Script:

-------

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LET var_TWOYEARSBACK=today()-(2*364);

LET var_YEARAHEAD=today() + 364;

LET var_DAYSNUM = $(var_YEARAHEAD) - $(var_TWOYEARSBACK);

TEMP:

LOAD

Date($(var_TWOYEARSBACK)+ IterNo()-1,'MM/DD/YYYY') as TempDate

AutoGenerate 1 While IterNo() <= $(var_DAYSNUM);

LOAD

TempDate as Date_Key,

Day(TempDate) as Value_For_Date,

Year(TempDate) as Year_Of_Date,

Month(TempDate) as Month_Of_Date,

Date(TempDate-364) as LastYear_Date

RESIDENT TEMP;

Chart Object: With Two Dimensions and Two Expressions

-------------------------------------------------------------------------------

Dimension 1: CY_Date

Date_Key

Dimension 2: Last_Year_Date

=Date((Date_Key-364),'MM/DD/YYYY')

Expression 1: CY_Date_Value

=sum(Value_For_Date)

Expression 2: LY_Date_Value

=sum({1}{<Date_Key={"=$(=Date((Date_Key-364),'MM/DD/YYYY')))"}>}Value_For_Date)

Re: Comparing This Year Date's Value with Last Year Same Date Value

tyagishaila — Fri, 23 Jan 2015 05:36:09 GMT

sum(Value_For_Date)

why are you adding days here?

=sum({1}{<Date_Key={"=$(=Date((Date_Key-364),'MM/DD/YYYY')))"}>}Value_For_Date)

This expression must be written as

=sum({1<Date_Key={LastYear_Date}>}Value_For_Date)

Re: Comparing This Year Date's Value with Last Year Same Date Value

Fri, 23 Jan 2015 14:31:57 GMT

Thanks for looking at this. But your expression does not work. Can you validate please ? For simplicity what I have done is the Value_for_Date is always the day number. So for example for Feb 1, 2015 the Value_for_Date should be 1, the previous year same date is Feb 2, 2014 and so the Value_for_date should be 2. The expression you gave, always gives a value of 0.

Re: Comparing This Year Date's Value with Last Year Same Date Value

tyagishaila — Tue, 27 Jan 2015 05:23:24 GMT

Sorry last one was not correct. It may be work..

Expression was not wrong.. There was the problem of Value_For_Date,

CY_Value = sum({1<Date_Key>}Day(Date_Key))

LY_Value = sum({1<LastYear_Date>}Day(LastYear_Date))