previous week calculation in set analysis

[stephenedberkg]

Hi

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)

[Frank_Hartmann]

have a look at the attached sample.

hope this helps

edit: I have added a new version of qvw

[stephenedberkg]

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

[agni_gold]

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)

[Frank_Hartmann · Accepted solution]

Ok,

i attached the scriptfile.

1. Create Straight table with dimension: CalendarWeekAndYear

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

or

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