topic Re: Load previous week data as additional column in QlikView

Load previous week data as additional column

Anonymous — Wed, 26 Jul 2017 16:26:37 GMT

Hi all!

I have a problem that I was trying to solve for a few days, but still cannot get it correct when I load data.

I have current table like this:

Opportunity No	Opp. Value	Script Execution Date	Script Execution Week
123	1000	2017-7-1	2017-26
123	1200	2017-7-8	2017-27
123	1300	2017-7-15	2017-28

I would like to have a table loaded like this:

Opportunity No	Opp. Value	Previous Week Opp Value	Script Execution Date	Script Execution Week	Prev Script Execution Week
123	1000	null	2017-7-1	2017-26	2017-25
123	1200	1000	2017-7-8	2017-27	2017-26
123	1300	1200	2017-7-15	2017-28	2017-27

I have all figured out, except how to load “Previous Week Opp Value”.

For some reason I am stuck with it :/, and it really should not be that difficult… I would appreciate any suggestion.

Thank you!!

Re: Load previous week data as additional column

Anonymous — Wed, 26 Jul 2017 16:32:24 GMT

Just to add the Load script:

Load Opportunity_No,

Opp_Value,

Script_Execution_Date,

Script_Execution_Week,

If(week(Script_Execution_Date)=1, Year(Script_Execution_Date)-1 & '-' & week(Script_Execution_Date - 7), Year(Script_Execution_Date) & '-' & week(Script_Execution_Date - 7)) as Previous_Script_Execution_Week;

Re: Load previous week data as additional column

Anonymous — Wed, 26 Jul 2017 19:11:25 GMT

Please look at the attached qvw.

Re: Load previous week data as additional column

Anonymous — Wed, 26 Jul 2017 19:58:09 GMT

Hi Mouna,

I will take a look at this and try to apply to my Load script. I have Qlik Sense so I cannot open the file you have attached as your solution. I will let you know if it works.

Thanks!

Re: Load previous week data as additional column

effinty2112 — Wed, 26 Jul 2017 20:13:27 GMT

Hi Gordana

This script will work as long as the data is loaded in date order:

Data:

LOAD [Opportunity No],

[Opp. Value],

Previous([Opp. Value]) as [Previous Week Opp Value],

Date([Script Execution Date],'YYYY-M-D') as [Script Execution Date],

Year(Date([Script Execution Date],'YYYY-M-D')) & '-' & Week(Date([Script Execution Date],'YYYY-M-D')) as [Script Execution Week],

Year(Date([Script Execution Date],'YYYY-M-D')-7) & '-' & Week(Date([Script Execution Date],'YYYY-M-D')-7) as [Prev Script Execution Week]

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

You can keep the script really simple:

Data:

LOAD [Opportunity No],

[Opp. Value],

Date([Script Execution Date],'YYYY-M-D') as [Script Execution Date]

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

and use chart expressions:

Opportunity No	Opp. Value	Previous Week Opp Value	Script Execution Date	Script Execution Week	Prev Script Execution Week
123	1000		2017-7-1	2017-26	2017-25
123	1200	1000	2017-7-8	2017-27	2017-26
123	1300	1200	2017-7-15	2017-28	2017-27

Opportunity No

Opp. Value

Previous Week

Opp Value

Script Execution

Date

Script Execution

Week

Prev Script Execution

Week

123

1000

2017-7-1

2017-26

2017-25

123

1200

1000

2017-7-8

2017-27

2017-26

123

1300

1200

2017-7-15

2017-28

2017-27

Dimensions are [Opportunity No] and [Script Execution Date]

Expressions:

Opp. Value = sum([Opp. Value])

Previous Week Opp Value = Above(TOTAL sum([Opp. Value]))

Script Execution Week =

Year(Date([Script Execution Date],'YYYY-M-D')) & '-' & Week(Date([Script Execution Date],'YYYY-M-D'))

Prev Script Execution Week=

Year(Date([Script Execution Date],'YYYY-M-D')-7) & '-' & Week(Date([Script Execution Date],'YYYY-M-D')-7)

Regards

Andrew

Re: Load previous week data as additional column

Anonymous — Wed, 26 Jul 2017 20:40:37 GMT

Thank you so much! It worked great, that is exactly what I needed!

Re: Load previous week data as additional column

effinty2112 — Wed, 26 Jul 2017 21:00:03 GMT

Hi Gordana,

Really pleased to have helped!