Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

reivax31
Contributor III

Accumulation with limitation

Hi Qlikers,

In one of my charts I would like to do an accumulation but then at a certain point I would like to stop the chart

So my dimension is a date (CanonicalDate) and my measure expression (Validated) is:

rangesum( above( Count({$<DateType={'AvailabilityDate'}>} IDNumber),0,rowno()))

Not working Example.png

As you can see my measure called Validated goes futher than a specific date that I stored in a variable vFirstDayOfWeek. What would be the expression for my measure in order to stop displaying the bar chart after my variable?

Also, as you can see for some dates I have no value, so no bar in my chart. What would be the expression in order to have a bar displayed for each day? With the line chart I dont have the problem because the lines are automatically linked. My table are build has explained in this topic Canonical Date

The expected result is:

Example.png

Thanks for your help!

1 Solution

Accepted Solutions
m_schindler
Contributor II

Re: Accumulation with limitation

Unfortunately, I was only able to solve this with an IF-expression (which may impact performance):

IF(CanonicalDate < '18/09/2017', rangesum(above(Count({$< DateType={'Target'}>} DS_Number), 0 ,rowno())))

Maybe someone else can come up with a better solution?

Mathias

6 Replies
m_schindler
Contributor II

Re: Accumulation with limitation

Hello xavier,

maybe try something like:

rangesum( above( Count({$<DateType={'AvailabilityDate'}, CanonicalDate={'<$(vFirstDayOfWeek)'}>} IDNumber),0,rowno()))

Mathias

reivax31
Contributor III

Re: Accumulation with limitation

Hi Mathias,

I tried your expression and the result is that all my data after vFirstDayOfWeek are not added to the cumulative count. But still it will display the bar after the date.


Before:

Not working Example.png

after:

Not working Example2.png

m_schindler
Contributor II

Re: Accumulation with limitation

Try this one then:

rangesum( {$<CanonicalDate={'<$(vFirstDayOfWeek)'}>} above( Count({$<DateType={'AvailabilityDate'}>} IDNumber),0,rowno()))

As long as I do not have an example .qvf-file / data I'm only able to guess .

Hope it works,

Mathias

reivax31
Contributor III

Re: Accumulation with limitation

Hi,

I just added a qvf sample at original post. Thanks for your help!

m_schindler
Contributor II

Re: Accumulation with limitation

Unfortunately, I was only able to solve this with an IF-expression (which may impact performance):

IF(CanonicalDate < '18/09/2017', rangesum(above(Count({$< DateType={'Target'}>} DS_Number), 0 ,rowno())))

Maybe someone else can come up with a better solution?

Mathias

reivax31
Contributor III

Re: Accumulation with limitation

Mathias this worked perfectly. I didn't know IF expression could impact performances. Anyway there isn't a lot of data in this particular case so I hope it will work fine.

Thanks for your great support !

Community Browser