Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
BI_Dev
Creator II
Creator II

Month to Month filter

Hi - I have historic data which updates once in a month and new months gets added to the Date column.

Ex:

ID Date Sales
1 1/1/2023 120
1 1/2/2023 200
1 1/3/2023 300
1 1/4/2023 200

 

I want to create a filter where, when I select Jan to Feb, 1/1/2023 and 1/2/2023 months should get selected and when I select Feb to March, 1/2/2023 and 1/3/2023 should get selected.

I tried the below..but did not work

if(year(Date) = year(today()) and match(num(month(Date)),'1','2'),'Jan-Feb'& year(today()),
if(year(Date) = year(today()) and match(num(month(Date)),'2','3'),'Feb-Mar'& year(today()),
if(year(Date) = year(today()) and match(num(month(Date)),'3','4'),'Mar-Apr'& year(today()))))

 

Jan-Feb WORKS, bur Feb-Mar only filters for March.

 

 

Thank you,

Labels (4)
9 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

Why not add one extra column to your dataset called

Month(Date) as Month

and then use this column Month as a filter?

Example:

Sales_temp:	//Generate random 100 Dates
Load
	Date(44015+RowNo()) as Date,	
	RowNo()				as ID,		//Some dummy ID
	Rand()*100 			as Sales	//Random Sales Value
AutoGenerate 100
;


Sales:
LOAD
	Date 
,	Month(Date) as Month
,	ID
,	Sales
Resident Sales_temp
;

Drop table Sales_temp;		//Drop dummy table

Results:

Stoyan_Terziev_0-1700057585340.png

 

 

Gabbar
Specialist
Specialist

Try This:
Table:
Load Distinct Date, Month(Date)&'-'&Month(Addmonths(Date,1)) as Filter_Column  resident main_table;
Concatenate(Table)
Load (Date - 1) as Date, Month(Date-1)&'-'&Month(Addmonths(Date-1,1)) as Filter_Column resident main_table;

Now link this Table and your main_table on Date Column and your filter Column should work.


BI_Dev
Creator II
Creator II
Author

Thank you but I only want to create a Month over Month filter using date column, where Jan to Feb is selected, 1/1/2023 and 1/2/2023 months should get filtered and when I select Feb to March, 1/2/2023 and 1/3/2023 should get filtered .

New Month  Filter Date
Jan-Feb 1/1/2023 & 1/2/2023
Feb-Mar 1/2/2023 & 1/3/2023
Mar- Apr 1/3/2023 & 1/4/2023
Apr-May 1/4/2023 & 1/5/2023
May-Jun 1/5/2023 & 1/6/2023
Jun-Jul 1/6/2023 & 1/7/2023
Jul-Aug 1/7/2023 & 1/8/2023
Aug-Sep 1/8/2023 & 1/9/2023
Sep-Oct 1/9/2023 & 1/10/2023
Oct-Nov 1/10/2023 & 1/11/2023
Nov-Dec 1/11/2023 & 1/12/2023

 

BI_Dev
Creator II
Creator II
Author

Thank you, but it gave the below result...Jan-Feb should have both Jan & Feb months.

BI_Dev_0-1700059593795.png

 

Aasir
Creator III
Creator III

if(Date >= MonthStart(today(), -1) and Date <= MonthEnd(today(), -1), 'Jan-Feb ' & Year(today()),
if(Date >= MonthStart(today(), 0) and Date <= MonthEnd(today(), 0), 'Feb-Mar ' & Year(today()),
if(Date >= MonthStart(today(), 1) and Date <= MonthEnd(today(), 1), 'Mar-Apr ' & Year(today()))))

Gabbar
Specialist
Specialist

Can you please try this again:-
Load Distinct Date, Month(Date)&'-'&Month(Addmonths(Date,1)) as Filter_Column resident main_table;
Concatenate(Table)
Load Date as Date, Month(Date-1)&'-'&Month(Addmonths(Date-1,1)) as Filter_Column resident main_table;

Also i hope you just have start date of each month in your datefield, Other wise please change it to monthstart before trying.

Stoyan_Terziev
Partner - Creator III
Partner - Creator III

Hey,

Then you need to build this data model:

Stoyan_Terziev_0-1700209523166.png

 

Here is the code:

Sales_temp:	//Generate random 100 Dates
Load
	Date(44015+RowNo()) as Date,	
	RowNo()				as ID,		//Some dummy ID
	Rand()*100 			as Sales	//Random Sales Value
AutoGenerate 100
;


Sales:
LOAD
	Date 
,	Month(Date) as Month
,	ID
,	Sales
Resident Sales_temp
;

Drop table Sales_temp;		//Drop dummy table


FOR vMonthNo = 1 to 12

	LET vMakeMonthNum = Num(Date(MakeDate(2023,$(vMonthNo),1)));

TRACE vMonthNo=$(vMonthNo);
TRACE vMakeMonth=$(vMakeMonth);

	LET vMonthPart1 = Text(Month($(vMakeMonthNum)));
	LET vMonthPart2 = Text(Month(AddMonths($(vMakeMonthNum),1)));
	
	
TRACE vMonthPart1=$(vMonthPart1);
TRACE vMonthPart2=$(vMonthPart2);
	
	CustomCalendar:
	Load
		Date($(vMakeMonthNum))					 		as Date
	,	'$(vMonthPart1)-$(vMonthPart2)' 				as MoM
	AutoGenerate 1;
	
	Load
		Date(AddMonths($(vMakeMonthNum),1))				as Date
	,	'$(vMonthPart1)-$(vMonthPart2)' 				as MoM
	AutoGenerate 1;

NEXT vMonthNo;
Vegar
MVP
MVP

Spinning on @Stoyan_Terziev's sample script I suggest this using `monthsname()`function for this (MOM0/MOM1) or create a pick() function (MoM2).

 

@BI_Dev 

Please refere to my example below.

Sales_temp:	//Generate random 100 Dates
Load
	Date(today()+RowNo()) as Date,	
	RowNo()				as ID,		//Some dummy ID
	Rand()*100 			as Sales	//Random Sales Value
AutoGenerate 365
;

Sales:
LOAD
	Date 
	,Month(Date) as Month
	,MonthsName(2,Date) as MoM0
	,dual(subfield(MonthsName(2,Date), ' ', 1),month(MonthsName(2,Date))) as MoM1
	,dual(pick(Month(Date),
		'Jan-Feb','Jan-Feb',
		'Mar-Apr','Mar-Apr',
		'May-Jun','May-Jun',
		'Jul-Aug','Jul-Aug',
		'Sep-Oct','Sep-Oct',
		'Nov-Dec','Nov-Dec'), 
		ceil( Month(Date)/2)) as MoM2
	,ID
	,Sales
Resident 
	Sales_temp
;
Drop table Sales_temp;		//Drop dummy table

 

The example will give you the output as shown in my QlikView table below (you will get the same result in Qlik Sense)

Vegar_0-1700225748217.png

-Vegar

 

whiteymcaces
Partner - Creator
Partner - Creator

All these answers are great, however, it is extremely difficult to answer specifically without seeing the structure of your data model.  Essentially, you need to create a field that has all the selection values you want to make, ie. 'Jan-Feb'; 'Feb-Mar' etc, and then associate these values with your Date field such that the value 'Jan-Feb' is associated with '01/01/2023' and '01/02/2023' etc. As you have shown above. The solution will be based on the structure of your data, so if you can provide more details, it will be easier to help you. Another thing to consider is multiple years, i.e. selecting 'Jan-Feb' will associate with '01/01' and '01/02' for all years.