topic Re: Wrong Pivot Table Values in QlikView

Wrong Pivot Table Values

miguelbraga — Thu, 22 Oct 2015 11:51:42 GMT

Hi community,

I have a new problem in my dashboard, when I enable the "Show Partial Sums" in the Presentation Tab in my Chart Properties, I get a wrong value. In the snapshot bellow you can see it clearly that there is something wrong with the value:

My dimensions are:

[Zona]
[CodLocal] labeled as [Cod. Local]
[CodEmpregado] labeled as [Cod. Funcionário]

My expressions are:

=sum(CustoReal) -> labeled as [€ Custo Real]
=sum(CustoReal)*num(sum({$<Zona=, CodLocal=, CodEmpregado=, data= >} DISTINCT Imputacao%)) -> labeled as [Imputação]

The [Imputacao%] values table:

What is the changes that I need to make to get the correct output result (1 316,41 €) for the totals of [Imputação] and (3 949,22 €)?

Can you experts help me? hic‌gwassenaar‌sunindia‌jontydkpi‌ can you help me? Anyone?

If anyone needs further more explanation, please ask it in the comment section bellow.

Regards,

Re: Wrong Pivot Table Values

sunny_talwar — Thu, 22 Oct 2015 11:54:32 GMT

Most likely you will need to use Dimensionality() function to use different expressions for your Partial Sum Rows. Do you have a qvw sample we can look at?

Re: Wrong Pivot Table Values

miguelbraga — Thu, 22 Oct 2015 12:01:52 GMT

I'm afraid I can't have the permission to share a qvw sample, due to privacy policy from my client . How can I use the Dimensionally() here?

Re: Wrong Pivot Table Values

hic — Thu, 22 Oct 2015 12:22:14 GMT

I wouldn't use dimensionality unless I wanted a different calculation on the totals - i.e. different from what I have on the rows. In your case, You want Sum(CustoReal) everywhere right?

No, if the subtotals are wrong, I'd say your data model is wrong. QlikView calculates correctly. It could e.g. be that the same dollar is asigned to several "Cod.Local" or to several "Zona". Then you would get exactly what you have here: A total that indeed is summed correctly in the fact table, but does not correspond to the sum of the lines in the pivot table.

My guess is that the "Zona" isn't connected to the data at all: Note that CBRASS is 398.50 no matter which "Zona" you look at.

HIC

PS If you create a text box with =Sum(CustoReal) in it, what do you get? This number is the true number in your data model.

Re: Wrong Pivot Table Values

miguelbraga — Thu, 22 Oct 2015 13:25:18 GMT

Hi sunindia,

Recently I've had the permission to develop a sample qvw so that I can share with my problem in it

Regards,

Re: Wrong Pivot Table Values

Thu, 22 Oct 2015 13:40:41 GMT

The problem is the table Zona, probability you joined Zona with Local and duplicated..

Re: Wrong Pivot Table Values

miguelbraga — Thu, 22 Oct 2015 13:48:26 GMT

If you see the Data Model, there is no duplication of Zona. Thanks for answering for my question

Re: Wrong Pivot Table Values

Thu, 22 Oct 2015 13:54:09 GMT

so, maybe the table Zona isn't relationship with Fact..

Re: Wrong Pivot Table Values

miguelbraga — Thu, 22 Oct 2015 15:01:57 GMT

Hi Marcos, my field Zone is linked with a table called PresenteFuturo and that's our fact table.

Re: Wrong Pivot Table Values

miguelbraga — Thu, 22 Oct 2015 15:02:47 GMT

Hi hic‌ have you view my qvw sample?

Re: Wrong Pivot Table Values

miguelbraga — Thu, 22 Oct 2015 16:33:11 GMT

This was the solution that I could by myself get:

With the expressions be like:

%Imputação -> =Sum(Imputacao%)
€ Imputação -> =Sum(Aggr(((tempo*1440)/60)*(ValorHora)*(Imputacao%), Zona, CodLocal, [CodEmpregado], [DataHora], [Saída]))
€ Custo Real -> =Sum(Aggr(((tempo*1440)/60)*(ValorHora), Zona, CodLocal, [CodEmpregado], [DataHora], [Saída]))

Hope someone find a similar problem in the future and that this answers may give you a similar solution. Have fun and good job people