Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
JustinDallas
Valued Contributor II

Count # of Weeks in Period

Hello Folks,

I am attempting to count the number of weeks in a period/month.  There is some logic to what a "Period" is so I can't use too many built-in and conventional methods.

Right now, when I run my script I'm left with this

BadPeriods.png

 

Whereas I want something similar to the numbers in Red.  Here is my script below, I'm confused why the LOAD COUNT("...") GROUP BY ... is giving me the number of DAYS in the period.

 

//[BEGIN SCRIPT]

LET vStartYear = 2019
;
LET vEndYear = 2022
;
LET vStartDate = Date(YearStart(MakeDate(Year(Today())-2)))
;
LET vEndDate = Date(YearStart(MakeDate(Year(Today())+2)))
;
TRACE 'Start date: $(vStartDate), End date: $(vEndDate)'
;

DATA:
LOAD *,
Floor(Num("Period Year")) & '/' & Floor(Num("Period")) as '%budget_period_key'
;
LOAD
Date('$(vStartDate)' + IterNo()) AS "Date",
DayStart(WeekEnd(Date('$(vStartDate)' + IterNo())) - 6) AS "Period WeekStart",
DayStart(WeekEnd(Date('$(vStartDate)' + IterNo()))) AS "Period WeekEnd",
Year(WeekEnd(Date('$(vStartDate)' + IterNo()))) AS "Period Year",
Num(Month(WeekEnd(Date('$(vStartDate)' + IterNo())))) AS "Period"
AutoGenerate 1
While '$(vStartDate)' + IterNo() <= '$(vEndDate)'
;

Blah3:
LOAD Count(DayStart(Floor(Num("Period WeekStart")))) AS 'Weeks In Period', %budget_period_key
RESIDENT DATA
GROUP BY %budget_period_key
;
Loosen Table Blah3
;

EXIT Script
;

//[END Script]

Labels (4)
1 Solution

Accepted Solutions
JustinDallas
Valued Contributor II

Re: Count # of Weeks in Period

I didn't have DISTINCT in the Count field.  I assumed that the Distinct wasn't necessary because the syntax highlighter left it black as opposed to turning it blue like it does other keywords.

 

DISTINCT.png

1 Reply
JustinDallas
Valued Contributor II

Re: Count # of Weeks in Period

I didn't have DISTINCT in the Count field.  I assumed that the Distinct wasn't necessary because the syntax highlighter left it black as opposed to turning it blue like it does other keywords.

 

DISTINCT.png