Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set analysis using week number of date dimension

Looking for help from the community:

I have a simple table that looks something like this:

Date  Type Value

09/09/2017  S  100

10/09/2017  S  100


I am trying to get a set analysis work that sums Values but only where the week number of the Date is equal to the current week number (in future I may change this to be some other variable).


What I've tried:

--------------------

Created a variable to get current week number: 

vCurWeek = Week([Date], 0)


Used the following measure in my table: 

Sum({$< Week([Date],0) = {$(vCurWeek)} >} Value)


But get no results.


I've tested the variable as well as using Week([Date], 0) in a text box/table and both are ok. I just can't get it to work as part of the set analysis, despite it looking pretty much the same as the correct answer in this post: Help to SET Analysis - get past 4 weeks


Thank you!

3 Replies
devarasu07
Master II
Master II

Try like this,

Fact:

load *,Week(Date) as Week;

load * Inline [

Date,  Type, Value

09/09/2017,  S,  100

10/09/2017,  S,  100

];

create variable as

vCurWeek

=Week(max(Date))

Set analysis expression as

=Sum({$< Week = {$(vCurWeek)} >} Value)

P.S: best way u can use master calendar script

vvira1316
Specialist II
Specialist II

HI,

You will not be able to use like you have it. The highlighted portion in red has to be replaced by a weeknumber field that you may have calculated in your script.

Sum({$< Week([Date],0) = {$(vCurWeek)} >} Value)


In the post you have included it is a field comparison to a variable

Count({<weeknumber = {$(vCurrentWeek)}>} solditems).

Providing a larger sample data and your script may help us to help you.

Best Regards,

Vijay

Anonymous
Not applicable
Author

mstDate:

LOAD * INLINE

[

minDate,  Type, Value

09/09/2017,  S,  100

10/09/2017,  S,  100

];

QuartersMap: 

MAPPING LOAD  

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

  AUTOGENERATE (12);  

 

Temp: 

Load     

min(date(minDate,'DD-MM-YYYY'),) as minDate,     

max(Today()) as maxDate 

Resident mstDate; 

drop table mstDate; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

Let vCurWeek   = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;   

TempCalendar: 

LOAD      

$(varMinDate) + Iterno()-1 As Num,      

Date($(varMinDate) + IterNo() - 1) as TempDate,

Date($(CurWeek) + IterNo()-1) as WeekDate     

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);               

MasterCalendar: 

Load               

TempDate AS  Date,             

  num( TempDate) AS Datenum,               

  week(TempDate) As Week,                

  Year(TempDate) As Year,                

  Month(TempDate)As Month,  

  Week(WeekDate) as CurWeek,             

  'Q' & Ceil (Month(TempDate)/3) as Quarter1 ,             

   num(Month(TempDate)) As MonthNum,              

   Day(TempDate) As Day,                

   YeartoDate(TempDate)*-1 as CurYTDFlag,                

   YeartoDate(TempDate,-1)*-1 as LastYTDFlag,      

   inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

    date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

     num(date(monthstart(TempDate), 'MMM-YYYY')) as MonthYearNum, 

     ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

     Week(weekstart(TempDate))&'-' & WeekYear(TempDate) as WeekYear, 

     WeekDay(TempDate) as WeekDay 

     Resident TempCalendar 

     Order By TempDate ASC; 

     Drop Table TempCalendar;

And

Set analysis expression as

=Sum({$< Week = {$(vCurWeek)} >} Value)