Create a filter for last 3 months , last 5 weeks on date for one specific chart qliksense

[Aspiring_Developer]

Hello All,

I have below chart :-

Dimension =date(floor(Date),'DD-MMM-YY') | Brand

Measure= Sum(Volume)/1000

Now, my requirement is to create a week filter (last 4 weeks, last 8 weeks , last 12 weeks , last 24 weeks ) and so on and I have data for last 3 years (2020 till 2022). And this filter should only work for this specific chart.

Similarly , I need to create one more filter for months(last 3 months, 6 months, 12 months, 24 months) and it should also also work for one specific chart given below :-

Dimension -MonthYear | Brand

Measure - sum(Volume)/1000

Both Date and Month Year fields are derived from master calendar:-

I have one single table and it is connect with master calendar in the data model.

Please find attached my data sheet .

Can anyone please help me on this ?

Thanks in advance

[Aspiring_Developer]

Can anyone please help me on this ?

Thanks

[SunilChauhan]

heck the attached file

Flag FIeld is FIlter. its for 3 month, 3-6 Month and 6-9 Months,  when you select 3 Months it show 3 Month,

When you select 3-6 then another 3 months

when you select Both 3 month and 3-6 month it will show last 6 months.

Hope this helps

[Aspiring_Developer]

Hello @SunilChauhan 

Many Thanks for your response.

I am unable to upload this qvf in my qliksense due to access restriction.

Could you please share code inside the script here please ?

Thanks again

[Masajnik]

I understand that what you want is not to modify the model but to solve an issue with a specific graph, if so, you could generate a master calendar that solves everything with set analysis, it is more there you could do other types of calculations such as YTD, R12 etc, confirm me if I understand correctly

[SunilChauhan]

its Same code as you. only Flag i have added in Bold . See the below Code

Test:
LOAD
Category,
Brand,
"Date",
"Dimension",
"Sub Dimension",
Volume
// "Month"
FROM [lib://Test/Flat File.xlsx]
(ooxml, embedded labels, table is Sheet1);

Temp:
Load
Min(Date) as MinDate,
Max(Date) as MaxDate
Resident Test;

Let vminDate=num(Peek('MinDate',0,Temp));
Let vmaxDate=num(Peek('MaxDate',-1,Temp));
Drop Table Temp;

Calendar:
Load $(vminDate)+iterno()-1 as num,
Date($(vminDate)+iterno()-1) as New_Date
Autogenerate 1 while $(vminDate)+iterno()-1<=$(vmaxDate);

Load*,
if(MonthName>MOnthName(Addmonths($(vmaxDate),-3)) and MonthName<=MonthName($(vmaxDate)),'3Month',
if(MonthName>MOnthName(Addmonths($(vmaxDate),-6)) and MonthName<=MOnthName(Addmonths($(vmaxDate),-3)),'3-6 Month',
if(MonthName>MOnthName(Addmonths($(vmaxDate),-9)) and MonthName<=MOnthName(Addmonths($(vmaxDate),-6)),'6-9 Month'))) as Flag;
Load Floor(New_Date) as Date,
Year(New_Date) as Year,
num(New_Date) as test,
Date(MonthStart(New_Date),'MMM-YYYY') as MonthYear,
Month(New_Date) as Month,
MonthName(New_Date) as MonthName,
Day(New_Date) as Day,
YearToDate(New_Date) as YTD,
Week(New_Date) as Week
Resident Calendar;
Drop Table Calendar;