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: 
CreepyCatLady
Creator
Creator

Line graph to show monthly data on a weekly load, without aggregating

Hi.

I need to create a line chart showing count of machines with a particular OS over time. The data load is weekly, but I need to show monthly. I am using a master calendar, and the problem is that when I use "Month," the chart adds up all the data loads for the month and inflates the numbers. I've searched the forums and found similar situations but none that exactly solve my problem.

The data load is every Monday, so what I need is to be able to show the first Monday of every month in the chart. Can anyone help with this?

1 Solution

Accepted Solutions
Vegar
MVP
MVP

You could flag every first monday of the month in your script like I've done in my script below.

LET vL.StartDate = yearstart(today(),-2);
LET vL.EndDate = today();
LOAD
%date,
MonthName(%date) as YearMonth,
Dayname(%date) as Date,
IF(%date = WeekStart(DayName(MonthStart(%date),6),0,0), dual('True',1), dual('False',0)) as _isFirstMondayInMonth
;
LOAD
rowno() as RowNo,
'$(vL.StartDate)'+recno() as %date
AutoGenerate '$(vL.EndDate)'-'$(vL.StartDate)'
;

After introducing the _isFirstMondayInMonth you could us  it in a field to filter your result either by selection or by SET analysis like below.

Count({$<_isFirstMondayInMonth={'True'}>}Machine)

 

View solution in original post

5 Replies
Vegar
MVP
MVP

You could flag every first monday of the month in your script like I've done in my script below.

LET vL.StartDate = yearstart(today(),-2);
LET vL.EndDate = today();
LOAD
%date,
MonthName(%date) as YearMonth,
Dayname(%date) as Date,
IF(%date = WeekStart(DayName(MonthStart(%date),6),0,0), dual('True',1), dual('False',0)) as _isFirstMondayInMonth
;
LOAD
rowno() as RowNo,
'$(vL.StartDate)'+recno() as %date
AutoGenerate '$(vL.EndDate)'-'$(vL.StartDate)'
;

After introducing the _isFirstMondayInMonth you could us  it in a field to filter your result either by selection or by SET analysis like below.

Count({$<_isFirstMondayInMonth={'True'}>}Machine)

 

CreepyCatLady
Creator
Creator
Author

I tried this and it isn't working for me, I am getting an equal number of "True" and "False" for _isFirstMondayInMonth, so it isn't bringing my numbers down. This is my Master Calendar script, have I done something wrong?

QuartersMap:
MAPPING LOAD

rowno() as Month,

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

AUTOGENERATE (12);

Temp:

Load

min(ASOFDATE) as minDate,

max(ASOFDATE) as maxDate

Resident V_DM_UNDERLYING1;

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

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

LET vL.StartDate = yearstart(today(),-2);

LET vL.EndDate = today();


DROP Table Temp;

TempCalendar:

LOAD

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

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

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

Load
%date,
MonthName(%date) as YearMonth,
Dayname(%date) as Date,
IF(%date = WeekStart(DayName(MonthStart(%date),6),0,0), dual('True',1), dual('False',0)) as _isFirstMondayInMonth
;
Load
rowno() as RowNo,
'$(vL.StartDate)'+recno() as %date
AutoGenerate '$(vL.EndDate)'-'$(vL.StartDate)';


MasterCalendar:

Load

TempDate AS ASOFDATE,

week(TempDate) As Week,

Year(TempDate) As Year,

MakeWeekDate(Year(TempDate), week(TempDate)) as WeekName,

Month(TempDate) As Month,

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,

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;

Kushal_Chawda

@Vegar  which editor you are using for colors? Manually giving ?

Vegar
MVP
MVP

You should in cooperate my suggestion into your master calendar. By the looks of it the flag field in the solution above is not attached to the rest of the data model. Remove my sample script from your script and add the flag calculation into your master calendar table. Like this :

 

MasterCalendar:
Load
TempDate AS ASOFDATE,
week(TempDate) As Week,
Year(TempDate) As Year,
MakeWeekDate(Year(TempDate), week(TempDate)) as WeekName,
Month(TempDate) As Month,
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,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
WeekStart(DayName(MonthStart(TempDate),6),0,0), dual('True',1), dual('False',0)) as _isFirstMondayInMonth
Resident TempCalendar
Order By TempDate ASC

 

@Kushal_Chawda : I had the patience to colour it manually using the styling tools in the community editor. I've suggested to the community corner to add Qlik syntax highlighting as a choice when adding code snipplets, but did not get the response I wanted for that suggestion . https://community.qlik.com/t5/Community-Corner/Qlik-Script-syntax-highlighter-for-code-samples-in-th...

Rohan
Specialist
Specialist

Hello again :

There is a small edit in my answer : First you filter for Mondays & then flag the 1st Monday for each month. The bold part is the edit.

Hi,

Try a simple flag where you tag the 1st Monday of every month. 

Temp :

Load

Date,

Weekday(Date) as DayCheck,

firstMonday;

Load

Monthname(Date) as MonthYear,

min(Date) as Date,

'True' as firstMonday 

 group by  Monthname(Date);

Load

Date

Resident MasterCalendar where wildmatch(Weekday(Date),'Monday') ;

Keep this table either keep this table on association with your Master Calendar or Left Join this to your Master Calendar on the basis of 'Date' field.

The DayCheck field is just to crosscheck/validate if the Date is actually Monday or not. 

Then in your measure use the 'firstMonday' field in your set analysis. 

Try this out & check if it works for you.

Regards,

Rohan.