Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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);
OR
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 |
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
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;
Please look at the attached qvw.
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!
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);
OR
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 |
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
Thank you so much! It worked great, that is exactly what I needed!
Hi Gordana,
Really pleased to have helped!