topic Re: Wrong Total in a Straight Table in QlikView

Wrong Total in a Straight Table

malmsteen1977 — Sat, 16 Nov 2024 20:36:55 GMT

Hello all,

I searched the community but I didn't get a satisfying answer.

I have a simple straight table with 3 rows:

- Amount previous year

- Amount current year

- The third row should only sum those Parts (Dimension PartID), which have a value >0 in previous AND current year.

I have an if-Clause to do this, which works correctly on dimension-base, but the TOTAL is wrong (2.947.990,80). When you sum up the correct Part-Values, you only get 2.808.454,80

The field "AcceptedAmount" in the Expression is based on single DocumentNumbers (DocNo) and Positions (PosNo) in the table XI_PurchFacts and aggregated on Parts (PartID as Dimension)

I already tried some aggr(sum)-Expressions, but the Total is still wrong.

Sample data attached. Any advice is appreciated 🙂

Re: Wrong Total in a Straight Table

sunny_talwar — Fri, 21 Jun 2019 12:33:35 GMT

Try this

Sum(Aggr( IF(Sum({<MainDateYear={$(=only(MainDateYear)-1)}>} AcceptedAmount) <>0 and Sum({<MainDateYear={$(=only(MainDateYear))}>} AcceptedAmount) <> 0, Sum({<MainDateYear={$(=only(MainDateYear)-1)}>}AcceptedAmount),0) , PartID))

Re: Wrong Total in a Straight Table

malmsteen1977 — Fri, 21 Jun 2019 12:50:23 GMT

Perfect Sunny. Thank you very much!!!

Re: Wrong Total in a Straight Table

sunny_talwar — Fri, 21 Jun 2019 12:53:12 GMT

In fact, since this is a straight table... you can continue to use this

IF(Sum({<MainDateYear={$(=only(MainDateYear)-1)}>} AcceptedAmount) <>0 and Sum({<MainDateYear={$(=only(MainDateYear))}>} AcceptedAmount) <> 0, Sum({<MainDateYear={$(=only(MainDateYear)-1)}>}AcceptedAmount),0)

but change the total mode to Sum of Rows

Re: Wrong Total in a Straight Table

malmsteen1977 — Fri, 21 Jun 2019 12:54:59 GMT

That's right, but when I change the Layout to Pivot, then I need your right answer.