Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
kishorj1982
Creator II
Creator II

Showing Last 36 months of data based on current Date

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

 

 

Labels (3)
5 Replies
zhadrakas
Specialist II
Specialist II

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
Partner - Contributor III
Partner - Contributor III

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
Specialist II
Specialist II

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
Partner - Contributor III
Partner - Contributor III

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
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.