Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

nslemmons
New Contributor II

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

Re: How to best load data for analysis of data over dates

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?

Highlighted

Re: How to best load data for analysis of data over dates

See these blog posts for some ideas:

Accumulations

The As-Of Table


talk is cheap, supply exceeds demand
nslemmons
New Contributor II

Re: How to best load data for analysis of data over dates

Thanks Gysbert. Those were very informative posts.

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

-Nate

nslemmons
New Contributor II

Re: How to best load data for analysis of data over dates

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..

-Nate

Community Browser