topic Re: Showing Last 36 months of data based on current Date in QlikView

Showing Last 36 months of data based on current Date

kishorj1982 — Sat, 16 Nov 2024 01:49:20 GMT

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

Re: Showing Last 36 months of data based on current Date

zhadrakas — Mon, 02 Dec 2019 10:38:19 GMT

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

Re: Showing Last 36 months of data based on current Date

Anismohamed32 — Tue, 03 Dec 2019 09:26:57 GMT

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.

Re: Showing Last 36 months of data based on current Date

zhadrakas — Tue, 03 Dec 2019 09:30:12 GMT

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

Re: Showing Last 36 months of data based on current Date

Anismohamed32 — Tue, 03 Dec 2019 09:33:34 GMT

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

Re: Showing Last 36 months of data based on current Date

Brett_Bleess — Tue, 10 Dec 2019 20:46:49 GMT

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