topic Re: getting wrong calc in the Totals for the Pivot table? in QlikView

getting wrong calc in the Totals for the Pivot table?

Wed, 21 Sep 2016 05:18:02 GMT

HI All,

In this report, I am having sales, target, call avg, % Ach , closing ..

% Ach will be always sales divided by target which i am having already calculated value, but in total also it should come total sales divide by the target,

but was showing sum of % Ach which is wrong it should show as 0.85..

Also for the call avg, but in the total it was showing sum, i need to get the avg of the calls is 59..

And for the closing it should come as the Last Month closing means feb Month in the Total as 75.

Month Selection will be the From Month as Jan & To Month as Feb..

For Sales & Target it getting total its correct..

How can i achieve Call avg & %ach in the Total...

Kindly help me on this..

Regards,

Helen

Re: getting wrong calc in the Totals for the Pivot table?

Gysbert_Wassenaar — Wed, 21 Sep 2016 05:44:22 GMT

If you want a sum then use the sum function, but if you want an average then use the avg function. See attached example.

Re: getting wrong calc in the Totals for the Pivot table?

Anil_Babu_Samineni — Wed, 21 Sep 2016 05:46:02 GMT

You expression is bit long

How about this?

Sum({<Month >} Sales)

From Below what are you expecting

OrderID	TypeName	Month	Sales
10248	Sales	Jan	63.55
10248	Sales	Jan	140.4
10248	Target	Jan	343.44
10248	%Ach	Jan	0.59
10248	calls avg	Jan	58
10248	closing	Jan	45
10249	Sales	Feb	205.2
10249	Target	Feb	250
10250	Sales	Feb	95.9
10250	%Ach	Feb	1.2
10250	calls avg	Feb	60
10250	closing	Feb	75

Re: getting wrong calc in the Totals for the Pivot table?

Wed, 21 Sep 2016 06:01:47 GMT

Thanks you, Gysbert.. it suits my requirement.

Here I am having one flag condition,

Example: In Flag having Prim & Sec ..

If select Prim need to show all the expressions as above.

If I select Sec then i dont want to see Closing & Calls avg 7 remainig i wanted to see..

Regards,

Helen

Re: getting wrong calc in the Totals for the Pivot table?

Anil_Babu_Samineni — Wed, 21 Sep 2016 06:06:33 GMT

Like this

For Ref, Please find attachment

Re: getting wrong calc in the Totals for the Pivot table?

Gysbert_Wassenaar — Wed, 21 Sep 2016 06:06:55 GMT

Example: In Flag having Prim & Sec ..

Which example? I don't see any Flag, Prim & Sec fields or values in the document you posted.

Re: getting wrong calc in the Totals for the Pivot table?

tresB — Wed, 21 Sep 2016 06:07:25 GMT

Use SecondaryDimensionality() like in attached sample. I have done it for one measure - %Ach, replicate similarly for rest.

If (SecondaryDimensionality()=0, If( TypeName='%Ach', Sum(total{<TypeName={'Sales'}>}Sales)/Sum(total {<TypeName={'Target'}>}Sales)),

sum({<Monthno={">= $(=num(month(date#(v_monthfrom,'MMM')))) <= $(=num(month(date#(v_monthto,'MMM'))))"}>}Sales)

)

For more measures you can replace IF with Pick()-Match().

Re: getting wrong calc in the Totals for the Pivot table?

Wed, 21 Sep 2016 06:13:15 GMT

Hi gysbert, I did'nt kept flag condition in that application..

But i need in that way, In my project app i am having like that

Re: getting wrong calc in the Totals for the Pivot table?

Wed, 21 Sep 2016 07:24:04 GMT

Hi gysbert, I did'nt kept flag condition in that application..

But i need in that way, In my project app i am having like that..

how can i hide other expressions.

kindly help me on this.

Regards,

Helen

Re: getting wrong calc in the Totals for the Pivot table?

Gysbert_Wassenaar — Wed, 21 Sep 2016 07:26:54 GMT

I don't understand what you want. I have no idea what this 'Flag' thing is you're talking about and how that influences the application.

Re: getting wrong calc in the Totals for the Pivot table?

Wed, 21 Sep 2016 07:47:58 GMT

HI Gysbert,

I have updated the above excel which is attached, included SalesType as Flag.

If it is Primary flag then Primary related type names,

If it is Primary & Seconadry flag then Seconadry ,Primary related type names,

Regards,

helen

Re: getting wrong calc in the Totals for the Pivot table?

Gysbert_Wassenaar — Wed, 21 Sep 2016 08:48:06 GMT

See attached qvw

Re: getting wrong calc in the Totals for the Pivot table?

Wed, 21 Sep 2016 10:08:35 GMT

Thanks Gysbert