32 Replies Latest reply: Nov 8, 2017 10:37 AM by Mike Slottje

# Full accumulation - showing a period

Hi guys,

I'm facing a problem in my line chart.

I want to show the accumulated sum of a bank balance, but only for this year.

To do so, I'm using the following script with MonthYear as dimension:

rangesum(above(sum([GL Amount USD]),0,rowno()))

This script is working fine when I'm showing the bank balance as a line chart over all the years in our database.

When I'm selecting to show only the current year, the script above is starting to accumulate with the first month of the current year as starting point.

How could I make a chart where the bank balance is fully accumulated over all the years, but where only the current year is displayed?

Cheers,

Mike

• ###### Re: Full accumulation - showing a period

Maybe=

rangesum(above(sum({<Year={"\$(=Year(Today(1)))"}>}[GL Amount USD]),0,rowno()))

• ###### Re: Full accumulation - showing a period

It starts to accumulate at january 2017, where the starting point should be in April 2009.

Is there a way to select the starting date for the accumulation, but only show the current year?

• ###### Re: Full accumulation - showing a period

Do you experts have an idea for this problem? gwassenaar mto stalwar1

mslottje wrote:

Hi guys,

I'm facing a problem in my line chart.

I want to show the accumulated sum of a bank balance, but only for this year.

To do so, I'm using the following script with MonthYear as dimension:

rangesum(above(sum([GL Amount USD]),0,rowno()))

This script is working fine when I'm showing the bank balance as a line chart over all the years in our database.

When I'm selecting to show only the current year, the script above is starting to accumulate with the first month of the current year as starting point.

How could I make a chart where the bank balance is fully accumulated over all the years, but where only the current year is displayed?

Cheers,

Mike

• ###### Re: Full accumulation - showing a period

Try this

RangeSum(Above(Sum({<Year, Date, MonthYear, Month>}[GL Amount USD]), 0, RowNo())) * Avg(1)

• ###### Re: Full accumulation - showing a period

Hi Sunny,

thanks for your help, unfortunately this expression doesn't change the outcome..

Any other ideas?

Or should I create an example file?

Kind regards,

Mike

• ###### Re: Full accumulation - showing a period

Which field are you making selection in?

• ###### Re: Full accumulation - showing a period

I have a line chart with MonthYear as dimension and the expression you gave me as measure.

I've made a selection in the dimension with the following if statement:

if(Year='2017', MonthYear)

• ###### Re: Full accumulation - showing a period

I think the above expression should work... I think if you can share a sample where you can show it isn't working, we might be able to look to see what is wrong

• ###### Re: Full accumulation - showing a period

Hi Sunny,

I'll get back to you later with an example file.

Mike

• ###### Re: Full accumulation - showing a period

Hi Sunny (stalwar1),

attached you'll find a qvf file with example data.

As you can see in the data, I have an amount per date.

This amount should be accumulated since the first available date.

I was able to do that with the following expression:

rangesum(above(sum([GL Amount USD]),0,rowno()))

As you can see in the first bar chart in the example qvf, this works perfectly when I'm showing the amount ever since the first available date.

Now, I only want to show the amount in 2017. When using the expression you gave me, the accumulation starts on the first date in 2017 and shows therefore not the numbers that I expect.

How can we make sure that the accumulation starts on the first available date, but only the current year is shown?

Kind regards,

Mike

• ###### Re: Full accumulation - showing a period

You are using calculated dimension, why? Try with Date and select Year  = 2017

But if you want to look at only 2017, regardless of selection, then try this

RangeSum(Above(Sum({<Year, Date, MonthYear, Month>}[GL Amount USD]), 0, RowNo())) * Avg({<Year = {2017}>}1)

• ###### Re: Full accumulation - showing a period

I used a calculation dimension to exclude the years before the current year, but this clearly was not what I should've done.

The example you've shown me is exactly what I was looking for, so many many many thanks for your help!

You're my hero of the day:)

Cheers,

Mike

• ###### Re: Full accumulation - showing a period

Hi Sunny (stalwar1),

still one question left..

Is it possible to make a line chart with month/week as dimension (x-axis) and 3 measures/lines (2017, 2016 & 2015) shown in the chart?

When I'm changing the dimension from date to month/week, I'm facing the old problem..

Mike

• ###### Re: Full accumulation - showing a period

You should be able to do this... can you update the sample to show the issue?

• ###### Re: Full accumulation - showing a period

Hi Sunny (stalwar1),

see the third chart in the attached and updated qvf file..

The third chart has month as dimension and 2017 en 2016 as measure. Unfortunately, these lines are equal.

The fourth chart has monthyear as dimension and work, but not as I wanted..

These lines should be shown separately in one chart with month as dimension on the x-axis.

Please let me know if anything is unclear..

Mike

• ###### Re: Full accumulation - showing a period

Can you explain as to what doesn't look right?

• ###### Re: Full accumulation - showing a period

Hi Sunny (stalwar1),

I want to show these lines together in a chart..

So I want a x-axis with 12 months and the lines who represent the amount per year.

See below an example (other fictive data set) of what I mean.

Hopefully this screenshot clarifies the case!

• ###### Re: Full accumulation - showing a period

Try this

Dimension

Month

Year

Expression

Only({<Year = {2017, 2016}>} Aggr(RangeSum(Above(Sum({<Year, Date, MonthYear, Month>}[GL Amount USD]), 0, RowNo())), Year, Month))

• ###### Re: Full accumulation - showing a period

Still not fully solved.. There are two lines in the chart now (as supposed to be), but as you can see in table next to or the chart 'accumulated 2, current year' above the chart, the values are not correct.

In example, January 2017 should've 1.355.380,49 as value, but now shows -708.742,09.

Unfortunately, I can't explain why Qlik is showing these numbers..

• ###### Re: Full accumulation - showing a period

That was completely my bad... try this

Only({<Year = {2017, 2016}>} Aggr(RangeSum(Above(TOTAL Sum({<Year, Date, MonthYear, Month>}[GL Amount USD]), 0, RowNo(TOTAL))), Year, Month))

• ###### Re: Full accumulation - showing a period

We're getting close, but the numbers of the fourth and the second chart still doesn't match..

The number in the box of Jan 2017, doesn't exist in the chart above. Is this number the average amount of January? And is it possible to change this number to the value on the last day of the month?

• ###### Re: Full accumulation - showing a period

Seems like a sorting issue

Only({<Year = {2017, 2016}>} Aggr(RangeSum(Above(TOTAL Sum({<Year, Date, MonthYear, Month>}[GL Amount USD]), 0, RowNo(TOTAL))), Year, (Month, (NUMERIC))))

• ###### Re: Full accumulation - showing a period

Wow Sunny, you're a god! This is going to help me very much and I'm really thankful for your help

Hopefully other Qlik users are also helped with your solution.

• ###### Re: Full accumulation - showing a period

Hahahaha, I am not god my friend... I am just a regular human being....

You also wanted to "change this number to the value on the last day of the month"? is this still needed or are we done?

• ###### Re: Full accumulation - showing a period

Hahaha for this moment you are for me!:)

The value that's shown now, is the value at the end of the month, so that's good for me.

Nevertheless, it could be interesting and worthy to know how change this to another point in the month (i.e. the begin).

But then this conversation is starting to look like a lecture, so up to you if you find that any interesting to figure out!

• ###### Re: Full accumulation - showing a period

Do you think if we move the chart down by a single month, that will give you what you are looking to get?

• ###### Re: Full accumulation - showing a period

I don't fully understand what you mean..

That's what we already did isn't it?

Oh edit *

How should we get current year as Year={\$(=Year(Today()))} and prior year as {\$(=Year(Today())-1)}etc. in this expression?

• ###### Re: Full accumulation - showing a period

May be this

Only({<Year = {'\$(=Year(Today()))', '\$(=Year(Today())-1)'}>} Aggr(RangeSum(Above(TOTAL Sum({<Year, Date, MonthYear, Month>}[GL Amount USD]), 0, RowNo(TOTAL))), Year, (Month, (NUMERIC))))

• ###### Re: Full accumulation - showing a period

Yep! That's it! You're officially the MVP today

• ###### Re: Full accumulation - showing a period

stalwar1 : MVP.

• ###### Re: Full accumulation - showing a period

Hi Gysbert,