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

How to best load data for analysis of data over dates

Hello all.

I've been working on creating a dashboard in Qlik Sense that will track some metrics related to our transportation operations. The data is specifically about drivers.

What I would like to do is be able to calculate values per driver, per plant, per week, prior week, month, prior month, prior 3 months, prior 6 months, prior year, etc.. possibly by quarters as well depending on how the data looks at the end. I'm planning to do this with set analysis in the measures of the charts/tables.

What I have is an excel file (Sample attached) that I use as the fact table.

I'm wondering if someone would have some advice on possibly the best way to load this data (possibly with set variables for different dimensions on the months, weeks, etc..)

Below is a sample set analysis expression I used to look at driver accidents per driver per plant for the current month in the data set. This works well but I can't show the yearMonth in the table and I can't seem to have it look at the prior month either.

Sum({<Category={"ACCIDENTS"}, total={"1"}, valueType={"driverFault"}, yearMonth={"$(=Max(yearMonth))"}>}(Value))/Avg(CountOfdriverID)

So this let me to ask if there is a better way to set up the data for analysis when dealing with specific dates but wanting to aggregate into months, quarters and so on.

4 Replies

You have been very descriptive of what you want, which is great, but I am still having difficulty understanding how you plan to do a data analysis with dates. Is there a particular output you might be interested in?


See these blog posts for some ideas:


The As-Of Table

talk is cheap, supply exceeds demand
Not applicable

Thanks Gysbert. Those were very informative posts.

I can see the application of the As-Of Month technique and I may employ that idea.


Not applicable

Hi Sunny.

Basically I want to design some metrics that are showing something like CSA points per driver per month. I would also like to take those same metrics and show them by prior month and prior 3 months, etc..