4 Replies Latest reply: May 25, 2016 12:54 PM by Nate Slemmons RSS

    How to best load data for analysis of data over dates

    Nate Slemmons

      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.