Hi ,
I have a data model with Dim_time (Calendar) in Qlik Sense.
It work well , but the problem is that : if i want all datas for 2019 year , to show data i select year 2019 and all months of this year to have a result. But for the user is not a best way.
Now i want to add a detached calendar which permit to user to select 2019 year and automatically select all months of this year.
I want a fields of detached calendar become a select field for user , when he selct 2019 year, automatically all monts a selected.
Please help me !
my script
Fact_table:
LOAD * Inline [
id,cost,order_date
1 ,200,'01/12/2017'
2 ,86 ,'01/015/2019'
3 ,900,'20/05/2019'
3 ,745, '01/12/2018'
];
Dim_product:
LOAD * Inline [
id,brand,qty
1 ,'action',5
2 ,'casino' ,5
3 ,'carrefour',8
3 ,'hp' ,12
];
DIM_TIME:
NoConcatenate
LOAD Distinct
DateId as order_date,
date(DateId,'DD/MM/YYYY') as Date,
day(DateId) as Jour,
month(DateId) as Mois,
num(month(DateId), '00') as Mois_Num,
MonthName(DateId) as Mois_Nom,
year(DateId) & num(month(DateId), '00') as AnnéeMois, // Année mois en cours
year(DateId)-1 & num(month(DateId), '00') as AnnéeMois_Prv_Y1,
year(DateId)-2 & num(month(DateId), '00') as AnnéeMois_Prv_Y2,
year(AddMonths(DateId,-1)) & num(month(AddMonths(DateId,-1)), '00') as AnnéeMois_Prv_M1,
year(DateId) as Année,
year(DateId)-1 as Année_P1,
year(DateId)-2 as Année_P2
Where Exists (order_date,DateId);
// Generate a temp table of dates
LOAD
mindate + IterNo() as DateId
While mindate + IterNo() <= maxdate;
LOAD
min(order_date) as mindate,
max(order_date) as maxdate;
LOAD Distinct order_date
RESIDENT Fact_table Order by order_date asc;
Qualify*;
DETACHED_CALENDAR:
load * Resident
DIM_TIME;
Exit Script;
