Showing Last 36 months of data based on current Date

[kishorj1982]

Hi All,

I have a Date field in my data model having YYYY-MM-DD format.

The requirement is to load the last 36 months of data based on the Today's date in the original table in the data model.

Could you please suggest how to move ahead.

Thanks

[zhadrakas]

Hey, 

i would go like this.

1) Load a table with all needed dates in your desired format

2) Load fact table with where exists(DATE)

3) drop table from 1)

LET vToday = num(today());
LET vMinDate = num(MonthStart(AddMonths(today(),-36)));

Reduce_Data:
LOAD date($(vMinDate)+ rowno()-1) AS DATE
AUTOGENERATE $(vToday)-$(vMinDate)+1;

Fact_Table:
Load *
from YOUR_ORGINAL_TABLE
where exists(DATE);

drop table Reduce_Data;

regards

tim

[Anismohamed32]

Hi, The above reply is absolutely perfect !! but there is another solution which you can look after !!

You can have a variable as App_start_date 

Let App_start_date= Date(num(MonthStart(AddMonths(today(),-36))),'YYYY-MM-DD');

Transaction:

Load * from Transaction_table where Date >= $(App_start_date);

// This will load the data only greater and equal to that app start date !!

both gives the absolute form of loading.

[zhadrakas]

yes you are right. But note that you loose the "optimized load" in your example.

thats why i did it with where exists()

regards

tim

[Anismohamed32]

Hi,

yes , you are right !! best way is to have where exists.

If a person wish to create a master calendar , where exists is optimized solution 

Variable is one another solution which may help when u load from many tables in the loaders.

Glad to connect with you !!

[Brett_Bleess]

If Tim's post got your solution working, be sure to return to the post and use the Accept as Solution button on his post to give him credit for the assistance and to let other Community Members know it worked for you.  If you are still working on things, leave an update on what you need.

Regards,
Brett