Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have dataset based current year dates. But to get the CY,CY+6M and CY+NY we have values against the current year date only. Hence I am not able to get the values for future dates since it is not available in the data set.
So I think we need to transform the data to get the desired result. So I need someone help in transforming the data set as mentioned in the excel. Please help me out for the same. It is little urgent.
I would do something like the following:
Data:
LOAD
Date(Date#(Date,'M/D/YYYY')) as Date,
[CY Actuals] as Value,
'Actual' as Type
FROM [Data Set.xlsx] (ooxml, embedded labels, table is CY) Where Len(Date)>0;
LOAD
AddYears(Date#(Date,'M/D/YYYY'),-1) as Date,
[CY-1 actuals] as Value,
'Actual' as Type
FROM [Data Set.xlsx] (ooxml, embedded labels, table is CY) Where Len(Date)>0;
LOAD
AddYears(Date#(Date,'M/D/YYYY'),-2) as Date,
[CY-2 actuals] as Value,
'Actual' as Type
FROM [Data Set.xlsx] (ooxml, embedded labels, table is CY) Where Len(Date)>0;
LOAD
AddYears(Date#(Date,'M/D/YYYY'),+1) as Date,
[CY+1 forecast] as Value,
'Forecast' as Type
FROM [Data Set.xlsx] (ooxml, embedded labels, table is CY) Where Len(Date)>0;
LOAD
Date#(Date,'M/D/YYYY') as Date,
[CY forecast] as Value,
'Forecast' as Type
FROM [Data Set.xlsx] (ooxml, embedded labels, table is CY) Where Len(Date)>0;
Calendar:
Load distinct
Date,
Year(Date) as Year,
Month(Date) as Month,
If(Year(Date)=Year(Today()),1,0) as IsCY,
If(Date>=YearStart(Today()) and Date<=AddMonths(YearEnd(Today()),6),1,0) as [IsCY+6M],
If(Date>=YearStart(Today()) and Date<=AddMonths(YearEnd(Today()),12),1,0) as [IsCY+NY]
Resident Data;
Can someone please help me out on this? It is very urgent...
I would do something like the following:
Data:
LOAD
Date(Date#(Date,'M/D/YYYY')) as Date,
[CY Actuals] as Value,
'Actual' as Type
FROM [Data Set.xlsx] (ooxml, embedded labels, table is CY) Where Len(Date)>0;
LOAD
AddYears(Date#(Date,'M/D/YYYY'),-1) as Date,
[CY-1 actuals] as Value,
'Actual' as Type
FROM [Data Set.xlsx] (ooxml, embedded labels, table is CY) Where Len(Date)>0;
LOAD
AddYears(Date#(Date,'M/D/YYYY'),-2) as Date,
[CY-2 actuals] as Value,
'Actual' as Type
FROM [Data Set.xlsx] (ooxml, embedded labels, table is CY) Where Len(Date)>0;
LOAD
AddYears(Date#(Date,'M/D/YYYY'),+1) as Date,
[CY+1 forecast] as Value,
'Forecast' as Type
FROM [Data Set.xlsx] (ooxml, embedded labels, table is CY) Where Len(Date)>0;
LOAD
Date#(Date,'M/D/YYYY') as Date,
[CY forecast] as Value,
'Forecast' as Type
FROM [Data Set.xlsx] (ooxml, embedded labels, table is CY) Where Len(Date)>0;
Calendar:
Load distinct
Date,
Year(Date) as Year,
Month(Date) as Month,
If(Year(Date)=Year(Today()),1,0) as IsCY,
If(Date>=YearStart(Today()) and Date<=AddMonths(YearEnd(Today()),6),1,0) as [IsCY+6M],
If(Date>=YearStart(Today()) and Date<=AddMonths(YearEnd(Today()),12),1,0) as [IsCY+NY]
Resident Data;
Thanks for the help.. I will check and update you soon...
I believe this will only can be achieved using the script. It cannot be achieved in the UI? Please confirm...