Historical Data In Qlikview

[poluvidyasagar]

Hi,

I have data set which keeps changing everyday. But, the data set does not have date field when it is changed. 

Example Data set:

Last week (week 45) , data looks like this:

Data1:

Region	Product	Sale	Cost
Asia	Apples	100	50
Asia	Oranges	100	100
Asia	Mangoes	200	50
USA	Apples	100	100
USA	Oranges	200	50
USA	Mangoes	200	100
Europe	Apples	200	50
Europe	Oranges	100	100
Europe	Mangoes	200	50
Europe	Bananas	300	50

Script:

Load

Region,

Product,

Sale,

Cost,

(Sale-Cost)  as Profit

From Data1:

Result Table:

It will be a straight table with dimensions as region and measure as Sum(profit).

Region	Profit
Asia	200
Europe	550
USA	250

But all the above results are for week 45. Now for  week (46), my data may look like this:

Region	Product	Sale	Cost
Asia	Apples	100	100
Asia	Oranges	150	200
Asia	Mangoes	200	100
USA	Apples	250	200
USA	Oranges	300	100
USA	Mangoes	350	500
Europe	Apples	400	100
Europe	Oranges	450	200
Europe	Mangoes	300	100
Europe	Bananas	300	100

I would like my result table to look the same way as above but time i would like to see my old results for week 45 as well:

Region	Week	Profit
Asia	46	50
Europe	46	950
USA	46	100
Asia	45	200
Europe	45	550
USA	45	250

How do i achieve this?

Remember,  the week number is unknown. I would that to be a calculated field based on the run date of the script.

Can someone help me how to do this?

Thanks,

Vidya

[juraj_misina]

Hi,

you can use Today() and Week() function to calculate week.

Load
Week(Today()) as Week, //shows just week number, like 45
WeekName(Today()) as WeekName //shows year as well: 2018-45
*
From SourceTable;

Be carefull with first week of year, it can get tricky (but there's a setting for that). See Broken Weeks.

[Vegar]

You can create a week dimension in the scripted as Juraj_misina wrote before me.

Then you will need to store your weekly extract into qvd files. I would recommend you to look into the topic incremental load to limit which data you need to store away for the future.