Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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)
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.
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))