Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have just started using Qlikview a few weeks ago. I am looking for a help from expert Qlikview users.
I am working to create simple dashboards. I have two excel files; one include records of number of car accidents and the other one for number of car movements. I have connected the tables in the following way:
Car_Accident_Data:
LOAD
Accident_ID,
Accident_Date,
Department,
Accident_Category,
Sub_Category,
Title,
Car_No,
Model,
Location,
NUM(Date(floor(Accident_Date),'DD/MMM/YYYY')) AS DateValue
FROM
[Working Files\Occurrence_Data_Main.xlsx]
(ooxml, embedded labels, table is Car_Accident_Data);
Car_Movement_Data:
LOAD
Movement_Date,
Car_No,
DEP,
ARR,
NUM(Date(floor(Movement_Date),'DD/MMM/YYYY')) AS DateValue
FROM
[Working Files\Flight_Cycles_Main.xlsx]
(ooxml, embedded labels, table is Car_Movement_Data);
I have the following as Master Calendar;
LET varMinDate = Num(Peek ('Accident_Date',0,'Car_Accident_Data'));
LET varMaxDate = Num(Peek('Accident_Date',-1,'Car_Accident_Data'));
LET varToday = Num(Today());
//************TempCalendar***********
TempCalendar:
LOAD $(varMinDate) + rowno() - 1 AS DateNumber,
date ($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE $(varToday) - $(varMinDate) + 1;
//*************Master Calendar************
CAL:
LOAD TempDate AS Date,
//TempDate AS FLT_Date,
//TempDate AS Occ_Date,
NUM(Date(floor(TempDate),'DD-MM-YYYY')) AS DateValue,
Week (TempDate) AS Week,
Year (TempDate) AS Year,
Month (TempDate) AS Month,
Day (TempDate) AS Day,
Weekday (TempDate) AS WeekDay,
'Q' & Ceil(Month (TempDate)/3) AS Quarter,
Year (TempDate)&'-Q' & Ceil(Month (TempDate)/3) AS YearQuarter,
Date( Monthstart (TempDate), 'MMM-YYYY') AS MonthYear,
Week (TempDate) & '-' & Year (TempDate) AS WeekYear
Resident TempCalendar
Order by TempDate ASC;
Drop Table TempCalendar;
I am not sure what wrong I am doing here... when there is no accident data (Count_Accident), the date fields of both Accident_Date and Movement_Date show Null. In turn Count_Car_Movements also show Null (shows 0 here as I have checked Show All Values under Dimensions), even though there are car movements every day. So when there is no accident, I must have number of car movements to calculate rate, such as accident rate based on total car movements per month, quarter, and year. Due to this issue, I am not getting the correct rate.
Can any experts help me sort this issues? I have been trying and looking around in the internet for a week now.
Thank you advance.
let me rephrase that
there are time you want to use {1} as you dont want the numbers to change if any filters are made. but for your requirement, you do want filters so be selective where you place the ignored field (it will depend on your requirements or specific metrics)
@edwin Thank you so much for your well explanations and patience. I am new to Qlikview and it is taking time to grab things at once.
Its working now. Thank you and looking forward for more assistance from you in future as well.
yw and gl