Month to Month filter

[BI_Dev]

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,

[Stoyan_Terziev]

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:

[Gabbar]

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]

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]

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

[Aasir]

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]

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]

Hey,

Then you need to build this data model:

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]

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

[whiteymcaces]

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.