Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sydtriman
New Contributor III

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
Honored Contributor II

Re: Set analysis using week number of date dimension

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
Valued Contributor II

Re: Set analysis using week number of date dimension

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

momin_tahemas
Contributor

Re: Set analysis using week number of date dimension

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)