topic Re: Help me please__Why is the answer in the regular table not equal to the Pivot table? in QlikView

Help me please__Why is the answer in the regular table not equal to the Pivot table?

penpayom — Tue, 14 Aug 2018 12:13:26 GMT

Help me please

Why is the answer in the regular table not equal to the Pivot table?

This my code in Expression Pivot table

=aggr(sum(DISTINCT(Fabric_QTY)*(ITEM_CONTAIN)/100),YARN_ITEM,Yarn_week)

Excample Yarn item = CVC20/1R week 18

Re: Help me please__Why is the answer in the regular table not equal to the Pivot table?

Or — Tue, 14 Aug 2018 12:21:07 GMT

I wasn't able to understand what you're trying to do here.

You are comparing two different formulas. Why should they give the same result?

Additionally, you are using Sum(Distinct ...) which is almost always a bad idea since the same value might repeat multiple times.

Re: Help me please__Why is the answer in the regular table not equal to the Pivot table?

marcus_sommer — Tue, 14 Aug 2018 12:32:18 GMT

There are various things noticeable. At first the distinct within the sum() like orsh_ hinted. Further the dimensions within the second table aren't the same like in the aggr and then it missed an outer aggregation.

- Marcus

Re: Help me please__Why is the answer in the regular table not equal to the Pivot table?

penpayom — Wed, 15 Aug 2018 01:04:56 GMT

I tried the same formula with the table below. But the value does not match. So try to group with aggr. What formula should I use to calculate?

Re: Help me please__Why is the answer in the regular table not equal to the Pivot table?

rwunderlich — Wed, 15 Aug 2018 04:45:15 GMT

Shouldn't you just use:

=sum(Fabric_QTY *(ITEM_CONTAIN/100))

for both tables?

-Rob

Re: Help me please__Why is the answer in the regular table not equal to the Pivot table?

BalaBhaskar_Qlik — Wed, 15 Aug 2018 05:25:35 GMT

May be this:

=Aggr(Sum(Fabric_QTY * (ITEM_CONTAIN/100)),YARN_ITEM)

Re: Help me please__Why is the answer in the regular table not equal to the Pivot table?

rwunderlich — Wed, 15 Aug 2018 15:16:24 GMT

Bala, why Aggr()?

-Rob