Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rahnajuk
New Contributor

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 numberworks startworks end
3761857--
37674676.8.2018 7:50-
37678336.8.2018 7:48-
37678606.8.2018 7:47-
37678726.8.2018 7:518.8.2018 13:51
37679036.8.2018 7:20-
376873216.4.2018 7:19-
376899519.4.2018 13:26-
376974330.7.2018 8:47-
3774993--
3779187--
377930926.4.2018 7:0520.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!

7 Replies
dberkesacn
Contributor III

Re: Accumulation with dates

Hi,

check this formula.

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


Also some vid:

https://www.youtube.com/watch?v=AlT_jAMsiGU


Best,

Dan

rahnajuk
New Contributor

Re: Accumulation with dates

Thanks for you reply!

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

paola_valenti
New Contributor III

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

Please check also hic‌'s document,

IntervalMatch and Slowly Changing Dimensions

Re: Accumulation with dates

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

rahnajuk
New Contributor

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.

paola_valenti
New Contributor III

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)

-link the tables with IntervalMatch

I created a chart as you asked with 3 expressionWorkDays_chart.PNG

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)

Community Browser