Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
reivax31
Partner - Creator III
Partner - Creator 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_s
Partner - Creator II
Partner - Creator II

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

View solution in original post

6 Replies
m_s
Partner - Creator II
Partner - Creator II

Hello xavier,

maybe try something like:

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

Mathias

reivax31
Partner - Creator III
Partner - Creator III
Author

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_s
Partner - Creator II
Partner - Creator II

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
Partner - Creator III
Partner - Creator III
Author

Hi,

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

m_s
Partner - Creator II
Partner - Creator II

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
Partner - Creator III
Partner - Creator III
Author

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 !