topic Re: How to best load data for analysis of data over dates in App Development

How to best load data for analysis of data over dates

Anonymous — Thu, 19 May 2016 16:48:37 GMT

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.

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

sunny_talwar — Sat, 21 May 2016 00:24:46 GMT

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?

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

Gysbert_Wassenaar — Sat, 21 May 2016 08:36:05 GMT

See these blog posts for some ideas:

Accumulations‌

The As-Of Table

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

Anonymous — Wed, 25 May 2016 16:54:07 GMT

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

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

Anonymous — Wed, 25 May 2016 16:55:42 GMT

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