Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

marcus_steggall
New Contributor III

Accumulation percentage based on 12 month totals!

Afternoon, hope all are well

I am trying to do a cumulative bar graph, across a rolling 12 months!

On the Expressions tab, it's easy to change each expression to "Accumulate" 12 steps (months) back.

However for the final Profit %, this won't work as is; percentages just become greater.

Even if you did no accumulation on the expressions tab, this would only do the calculation for the month only.

As you can see on the two straight tables, the 2018 Apr percentage is 53% which is correct for the month.

However a cumulative percentage for last 12 months to 2018 Apr, should be 34%.

How can I get that percentage working correctly, based on 12 month totals, at the month selected?

Any help or advice, appreciated

1 Solution

Accepted Solutions
MVP
MVP

Re: Accumulation percentage based on 12 month totals!

Try this with no accumulation

=RangeSum(Above(Sum({<[Period_Sort]={">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))"},Month= ,Year_YYYY= ,Quarter_QQ= ,


Customer={"=round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))<=$(vMinLimit)

or

round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))>=$(vMaxLimit)"}>} Profit), 0, 12))



/


RangeSum(Above(Sum({<[Period_Sort]={">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))"},Month= ,Year_YYYY= ,Quarter_QQ= ,


Customer={"=round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))<=$(vMinLimit)

or

round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))>=$(vMaxLimit)"}>} Revenue), 0, 12))

Capture.PNG

2 Replies
MVP
MVP

Re: Accumulation percentage based on 12 month totals!

Try this with no accumulation

=RangeSum(Above(Sum({<[Period_Sort]={">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))"},Month= ,Year_YYYY= ,Quarter_QQ= ,


Customer={"=round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))<=$(vMinLimit)

or

round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))>=$(vMaxLimit)"}>} Profit), 0, 12))



/


RangeSum(Above(Sum({<[Period_Sort]={">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))"},Month= ,Year_YYYY= ,Quarter_QQ= ,


Customer={"=round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))<=$(vMinLimit)

or

round(Sum({<[Period_Sort]={"">=$(=max([Period_Sort])-11)<=$(=max([Period_Sort]))""},Month= ,Year_YYYY= ,Quarter_QQ= >}Revenue))>=$(vMaxLimit)"}>} Revenue), 0, 12))

Capture.PNG

marcus_steggall
New Contributor III

Re: Accumulation percentage based on 12 month totals!

ahhh the range above function, excellent am pleased to report this also works.

Nice one pal, appreciated as always for quick response ands solution