topic Re: Issues with Full Accumulation in QlikView

Issues with Full Accumulation

Fri, 02 Dec 2016 20:56:20 GMT

Hello

As per the attached images, Full Accumulation is not being properly done. Final total must be 403, but it is 383 (blue only).

If there had been missing values, it would have reflected in both graphs.

What might be the reason for this discrepancy?

Thanks in advance!

Re: Issues with Full Accumulation

Gysbert_Wassenaar — Sat, 03 Dec 2016 08:10:12 GMT

Please post a small qlikview document that demonstrates this problem.

Re: Issues with Full Accumulation

sunny_talwar — Sat, 03 Dec 2016 12:10:22 GMT

What is your expression what is the dimensions? Just looking at the image doesn't give us any information. Like Gysbert mentioned a sample to play around will be really helpful.

Best,

Sunny

Re: Issues with Full Accumulation

Anonymous — Mon, 05 Dec 2016 08:56:36 GMT

Hi Kamil,

It appears to be issue in data model. Please post sample data.

Best regards,

Kaveri

Re: Issues with Full Accumulation

Thu, 23 Feb 2017 15:18:36 GMT

PS. Thank you for your concern. Yet, so sorry for such a late reply.

Re: Issues with Full Accumulation

Thu, 23 Feb 2017 15:20:44 GMT

gwassenaar‌ kaveri.bhar123‌ stalwar1‌

Re: Issues with Full Accumulation

Gysbert_Wassenaar — Thu, 23 Feb 2017 16:05:11 GMT

See attached example.

Re: Issues with Full Accumulation

Anonymous — Thu, 23 Feb 2017 19:27:41 GMT

Is it possible to solve this problem just with the help of diagram functions?

I tried with above, rangesum, aggr... without success...

The problem is that you have to accumulate each color dimension separetely.

Any ideas?

Re: Issues with Full Accumulation

Fri, 24 Feb 2017 13:43:33 GMT

I tried that. Display looks fine. But then, filters do not apply i.e. particular chart is no more interactive

Re: Issues with Full Accumulation

Anonymous — Fri, 24 Feb 2017 15:58:35 GMT

that's why I asked for a solution using only diagram expressions...

(At the moment I have no idea how to solve it...)

Re: Issues with Full Accumulation

Anonymous — Mon, 27 Feb 2017 10:49:57 GMT

I finally found a solution. But it only works with QV12, because the sortable aggr() function is needed.

I will write a separate article explaining the issues.

TABLE:
LOAD Color,
Date as Datefield
FROM
[Full Accumulation.xlsx]
(ooxml, embedded labels, table is Sheet1);

cart:
LOAD Distinct Color as Color_cart Resident TABLE;
outer join //cart. product, due to no matching...
LOAD Distinct Datefield as Datefield_cart Resident TABLE;

left keep // do NOT use left join, because values will be distinct on both sides...
LOAD Datefield as Datefield_cart,
Datefield,
Color as Color_cart,
Color
Resident TABLE;
DROP Table TABLE;

the expression is (syntax check doesn't work for sortable aggr()):

aggr(rangesum( Above( Count(Color),0,RowNo()) ),(Color_cart,(NUMERIC, ASCENDING)),(Datefield_cart,(NUMERIC, ASCENDING)))

Re: Issues with Full Accumulation

Wed, 01 Mar 2017 20:15:20 GMT

This is working as expected.

Thank you so much

Re: Issues with Full Accumulation

Wed, 01 Mar 2017 20:17:02 GMT

Script is simple. It looks as expected.

But graph is not appearing in qv file that you uploaded. Can you please recheck.

Thank you

Re: Issues with Full Accumulation

Anonymous — Thu, 02 Mar 2017 17:49:10 GMT

Do you really use QlikView 12?

I explained the details here:

https://community.qlik.com/docs/DOC-18130

Re: Issues with Full Accumulation

Thu, 02 Mar 2017 22:30:31 GMT

Sorry. I am restricted to use QV 11.

But thanks for sharing that stuff

Re: Issues with Full Accumulation

ngrunoz — Sat, 25 Mar 2017 21:19:16 GMT

Hi. Could you help by explaining the principle behind the script

AsOf:
LOAD
DateAsOf,
Date
WHERE
Exists(DateLookup,Date)
AND Exists(DateLookup, DateAsOf)
;
LOAD
Date as DateAsOf,
Date(Date-IterNo()+1) as Date
RESIDENT
Calendar
WHILE
Date-IterNo()+1 >= $(vMinDate)
; .

I would also want my 2 dimension full accumulation to be restricted in a financial year by each financial year. for example my red, green e.t.c accumulate only in my financial year FY1415, then starts a new accumulation in FY1516 etc.

Re: Issues with Full Accumulation

Mon, 10 Apr 2017 17:01:12 GMT

Hello Gysbert

That script worked fine when I have Date as Dimension, but I am unable to modify it correctly when I have expression (e.g. 'Attribute 2') as Dimension.

Attached is an example. Sum with Full Accumulation should be 403, not 383.

Your help would be greatly appreciated once again.

Thank you so much. Have a great day

Best Regards

Kamil Majeed

Re: Issues with Full Accumulation

sunny_talwar — Mon, 10 Apr 2017 17:11:50 GMT

I think the issue is related to missing values.... Try this script

Table:

LOAD Feature,

[Attribute 1],

[Attribute 2],

AutoNumber([Attribute 1]&[Attribute 2]) as Key

FROM

[Testing.xlsx]

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD Distinct [Attribute 1]

Resident Table;

Left Join (Temp)

LOAD Distinct [Attribute 2]

Resident Table;

Concatenate(Table)

LOAD [Attribute 1],

[Attribute 2]

Resident Temp

Where not Exists(Key, AutoNumber([Attribute 1]&[Attribute 2]));

DROP Table Temp;

Re: Issues with Full Accumulation

Mon, 10 Apr 2017 18:44:40 GMT

It is working as expected.

Thank you

Re: Issues with Full Accumulation

Wed, 12 Apr 2017 21:42:33 GMT

I added an extra column 'Identifier' and results are not as expected. i.e. value of 'Total' (chart without accumulation) not matching with final value of chart with accumulation.