7 Replies Latest reply: Aug 13, 2018 12:03 PM by Anil Samineni

# Accumulation with dates

Hi, I'm quite new to Qlik Sense and I'm trying to do cumulative graph visualizing the status of our work orders of our project.

My data looks like this:

 Work order number works start works end 3761857 - - 3767467 6.8.2018 7:50 - 3767833 6.8.2018 7:48 - 3767860 6.8.2018 7:47 - 3767872 6.8.2018 7:51 8.8.2018 13:51 3767903 6.8.2018 7:20 - 3768732 16.4.2018 7:19 - 3768995 19.4.2018 13:26 - 3769743 30.7.2018 8:47 - 3774993 - - 3779187 - - 3779309 26.4.2018 7:05 20.6.2018 7:24 3789542 - - 3789549 - - 3789553 - - 3789564 - - 3789568 - - 3789572 - - 3789576 - -

Ideally I'd like to have a combo chart, which has dates in the x-axis (from 1.8.2018 - 1.10.2018), and three measures: 1) bar for opened work orders for each day, 2) bar for closed work orders per day, 3) a cumulative line showing amount of open work orders.

I have been really stuck with this for a few weeks now, any help would be highly appreciated!

• ###### Re: Accumulation with dates

Hi,

check this formula.

rangesum( above( sum([Data]),0,rowno()))

Also some vid:

Best,

Dan

• ###### Re: Accumulation with dates

I've tried with rangesum, but I'm not sure how to use it properly, as there is two dimensions (starting date and ending date).

• ###### Re: Accumulation with dates

I think you need to create a Master Calendar with every single date in your period and link your data to this calendar.

To link your data to calendar you can use Interval Match, as described by hic in this post:

IntervalMatch

IntervalMatch and Slowly Changing Dimensions

• ###### Re: Accumulation with dates

Not sure, What is the business here. Would you able to share sample

• ###### Re: Accumulation with dates

Thanks for you answer. Sorry but due to sensitivity of my data, I'm not able to share a sample, other than what I've given in my first post.

We have work orders which have certain starting and ending date. I'm trying to do a chart which shows how many work orders we have open in each day. X-axis would be for the dates.

• ###### Re: Accumulation with dates

Look at this app:

-I create a simple calendar (from 1/4/2018 to 1/10/2018)

-load your data (null value are correct with date out from interval considered: start is 31/3/2018 and end is 2/10/2018)

I created a chart as you asked with 3 expression

• ###### Re: Accumulation with dates

I still wonder, What the business is. May be

LET vStart = Min(DateField);

LET vEnd = Max(DateField);

Then, Set analysis could be using

Sum({<DateField = {">=\$(=vStart)<=\$(=vEnd)"}>} Sales)