topic Re: previous week calculation in set analysis in QlikView

previous week calculation in set analysis

stephenedberkg — Wed, 25 Nov 2020 16:16:04 GMT

i have the week

The week start from 36th week of 2015 to 28th week of 2016

if i select the week 14 mean i want display the values of week 36th week 2015 to 13th week 2016

How to write in set analysis?

i wrote like below but i get only the max year values only.

sum({ <Year_UP= {"<=$(=Max(Year_UP))"},Week_UP= {"<$(=Max(Week_UP))"}>} sales)

Re: previous week calculation in set analysis

Frank_Hartmann — Sat, 23 Jul 2016 10:16:49 GMT

have a look at the attached sample.

hope this helps

edit: I have added a new version of qvw

Re: previous week calculation in set analysis

stephenedberkg — Sat, 23 Jul 2016 11:26:39 GMT

hi franky_h79

i am using personal edition .

i can't open the document

send me what you have did in this document ?

plz

Regards

Stephen G

Re: previous week calculation in set analysis

agni_gold — Sat, 23 Jul 2016 11:38:24 GMT

Here is the extract of above qvw posted by Frank

******************* Script ***************************

LOAD Year_UP,

Week_UP,

Sales,

num(Week_UP,'00') &'-'& Year_UP as CalendarWeekAndYear

FROM

[..\Microsoft Excel-Arbeitsblatt (neu).xlsx]

(ooxml, embedded labels, table is Tabelle1);

******************* Variable Used ***************************

vZeitraum49KW

vZeitraum19KW

*********************************Help Text*************************

By using the slider you can look at the previous weeks (with same range: 31 weeks)

If you want to adapt different weekrange, just replace the variable (vZeitraum**KW)

in the dimension/expression which was setup in script by the new variable, to adapt new weekrange

**************************** Created Slider Object******************************

Variable Used : vAktuelleKW

Min Value : -26

Max Value : 1

Static Step : 1

****************** Created First Straight Table **********************

Dimension : CalendarWeekAndYear

Expression : =sum({<WeekDiff={'>=$(vZeitraum49KW)<=$(vZeitraum19KW)'}>}Sales)

********************Created Second Straight Table ********************

Dimension : =If(WeekDiff>=$(vZeitraum49KW) and WeekDiff<=$(vZeitraum19KW),CalendarWeekAndYear)

Expression : sum(Sales)

Re: previous week calculation in set analysis

Frank_Hartmann — Sat, 23 Jul 2016 11:55:47 GMT

Ok,

i attached the scriptfile.

1. Create Straight table with dimension: CalendarWeekAndYear

2. Expression: sum({<WeekDiff={'>=$(vZeitraum49KW)<=$(vZeitraum19KW)'}>}Sales)

1. Create Straight table with dimension:

=If(WeekDiff>=$(vZeitraum49KW) and WeekDiff<=$(vZeitraum19KW),CalendarWeekAndYear)

2. Expression: sum(Sales)

I would recommend to create also a slider with Properties -->General -->Data -->Variables --> choose "vAktuelleKW"

set min: -26 and max=1, static step:1 (if you set the slider to 1 then your range is in scope)

By using the slider you can look at the previous weeks (with same range: 31 weeks)

If you want to adapt different weekrange, just replace the variable (vZeitraum**KW) in the dimension/expression which was setup in script by the new variable, to adapt new weekrange

hope this helps

edit: I also added the xlsx file