Accumulation with limitation

[reivax31]

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

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:

Thanks for your help!

[m_s]

Hello xavier,

maybe try something like:

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

Mathias

[reivax31]

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:

after:

[m_s]

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]

Hi,

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

[m_s · Accepted solution]

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]

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 !