topic Re: Qlik view - Column Accumulation in QlikView

Qlik view - Column Accumulation

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

See the above image:

I want to create an accumulation of Column Variation in Column Variation Accum.

I'm using the same expression for both columns, but in the last one I'm using the "Full Accumulation" that qlikview has, but it's not working.

Can you pease help

Thanks in advance,

Sílvia

Re: Qlik view - Column Accumulation

MK_QSL — Fri, 24 Feb 2017 12:42:09 GMT

Use this...Change SUM(Amount) to your respective measure aggregation.

Num((RangeSum(Above(SUM(Amount),0,RowNo())) - Above(RangeSum(Above(SUM(Amount),0,RowNo()))))/Above(RangeSum(Above(SUM(Amount),0,RowNo()))),'#0.00%')

Re: Qlik view - Column Accumulation

Anonymous — Fri, 24 Feb 2017 14:10:32 GMT

Hi,

I've attached a qvw sample.

Manish, still can't do it with your example.

Sílvia

Re: Qlik view - Column Accumulation

sunny_talwar — Fri, 24 Feb 2017 14:13:24 GMT

What is the expected output from the sample you have provided?

Re: Qlik view - Column Accumulation

Anonymous — Fri, 24 Feb 2017 14:19:29 GMT

Hi Sunny,

Iv'e attached a xls file, with a yellow column wich is my expected output.

I want for each code the accumulation of values in each month.

Re: Qlik view - Column Accumulation

sunny_talwar — Fri, 24 Feb 2017 14:29:01 GMT

Try this

RangeSum(Above(TOTAL $(fVar(*)), 0, RowNo(TOTAL)))

Re: Qlik view - Column Accumulation

Anonymous — Fri, 24 Feb 2017 14:32:31 GMT

Sunny,

It works for the first code,

When code changes, I to reset the accumulation.

I want the accumulation aggregated by code.

Is it possible?

Sílvia

Re: Qlik view - Column Accumulation

sunny_talwar — Fri, 24 Feb 2017 14:55:18 GMT

Try this:

RangeSum(Above(TOTAL $(fVar(*)), 0, Aggr(RowNo(), Code, Month)))

Re: Qlik view - Column Accumulation

Anonymous — Fri, 24 Feb 2017 15:06:46 GMT

Sunny,

Your expression works in table.

now, I want to put that in a line chart (see the attached)

When I only select a code it gives the right values, but when I show all codes, it does not.

Can you help?

Re: Qlik view - Column Accumulation

sunny_talwar — Fri, 24 Feb 2017 15:10:57 GMT

May be this for the chart

Aggr(RangeSum(Above(TOTAL $(fVar(*)), 0, RowNo())), Code, MonthYear)

Re: Qlik view - Column Accumulation

Anonymous — Fri, 24 Feb 2017 15:17:40 GMT

Sunny, you're my HERO.

But, one more question

Now in graph 2, when a code is in the same month in the same FactFaseActive, it does not show in chart.

It's expected to show the average of value of the two (in this case) codes that exists in that month in the same FactFaseActive.

Any thougths?

Sílvia

Re: Qlik view - Column Accumulation

sunny_talwar — Fri, 24 Feb 2017 15:22:13 GMT

What is the expression you are using for the second chart?

Re: Qlik view - Column Accumulation

Anonymous — Fri, 24 Feb 2017 15:25:22 GMT

First I've used the same as the first graph.

But I think it should be this one:

Aggr(RangeSum(Above(TOTAL $(fVar(*)), 0, RowNo())), FactFaseActive, MonthYear)

Right?

Because now my dimension is FactFaseActive.

But using this, the values are wrong, in that specific months it keeps showing the same value.

Re: Qlik view - Column Accumulation

sunny_talwar — Fri, 24 Feb 2017 15:33:54 GMT

Not sure, but may be this:

Avg(Aggr(RangeSum(Above(TOTAL $(fVar(*)), 0, RowNo())), Code, MonthYear))

Re: Qlik view - Column Accumulation

Anonymous — Fri, 24 Feb 2017 15:40:44 GMT

Yes, that works Sunny

Do you have any documentation, that explains cases like this, or the use of this functions together?

Re: Qlik view - Column Accumulation

sunny_talwar — Fri, 24 Feb 2017 15:50:54 GMT

Unfortunately, I don't have any holy grail I can share with you. Most of this is through trial and error over the last 2-3 years.

Re: Qlik view - Column Accumulation

Anonymous — Fri, 24 Feb 2017 15:54:43 GMT

So 2 more years through trial end error for me

Thanks a lot for your help Sunny.

I'll buy you a cup of coffe when you come to Portugal

Best Regards,

Sílvia

Re: Qlik view - Column Accumulation

sunny_talwar — Fri, 24 Feb 2017 15:57:19 GMT

Hahahaha sounds like a plan