Urgent: Help required to transform the data set for the future dates based on the current year data

[qlikviewforum]

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.

[qlikviewforum]

Can someone please help me out on this? It is very urgent...

[hic · Accepted solution]

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;

[qlikviewforum]

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