topic Re: totals in QlikView

totals

raadwiptec — Mon, 09 Nov 2015 10:41:20 GMT

hi All,

The below is pivot table I am using to build in qlikview

customer name	product	quantity	price	Total Q	Total price
sam	aa	12	500
kim	ss	10	235
abc	ss	1000	4445
def	dd	200	1500
Total

quantity and price are expressions.

basically I need the suggestion for one in Red.

on the right handside total Qua and total Price

on the below again - Total

any suggestions. I do not find any option to show up the totals

My second issue is to suppress the null and missing for expression values under pivot table.

Re: totals

Anonymous — Mon, 09 Nov 2015 10:45:24 GMT

On your Pivot Table presentation tick Show Partial Sums for the appropriate Dimension.

Re: totals

Anonymous — Mon, 09 Nov 2015 10:47:01 GMT

Also on the Presentation tab you'll find the Suppress Zero-Values and the Suppress Missing tick boxes.

Re: totals

Anonymous — Mon, 09 Nov 2015 10:50:24 GMT

for your total in red (below) use the Suggestion of Bill

for the toal on right handside define Expression sum(quantitiy*price), that gives you the total sum per product

Re: totals

Anonymous — Mon, 09 Nov 2015 10:56:14 GMT

See the attachment.

Under presentation tab - select dimension(Top left) - Show partial sum

Re: totals

himanshi — Mon, 09 Nov 2015 11:39:53 GMT

hiii,

you have options for both in presentation tab of chart properties. their is show partial sum option for totals you can apply it for the dimension according to which you want total and suppress zero values option for hiding null values.

Regards,

Himanshi Dubey

Re: totals

raadwiptec — Mon, 09 Nov 2015 11:57:00 GMT

partial sums are good.. but on the right hand side ..how can I have the same total quantity and total price on the right hand side.

Re: totals

raadwiptec — Mon, 09 Nov 2015 12:00:04 GMT

when I say total quantity on the right handside.. it should be something this

for example keeping months in pivot table. for jan it is 12

feb - 15

so I need to add all the columns for sam and then make the total of quantity he purchased for the year.

customer name	product	quantity jan	price	Total Q	Total price
sam	aa	12	500
kim	ss	10	235
abc	ss	1000	4445
def	dd	200

Re: totals

himanshi — Mon, 09 Nov 2015 12:02:00 GMT

hiii,

you can use above expression like

sum(price) for total price and sum(quantity) for total q.

Re: totals

himanshi — Mon, 09 Nov 2015 12:04:26 GMT

or you can try sum(column(3)) for total Q and sum(column(4)) for total price.

Re: totals

Anonymous — Mon, 09 Nov 2015 12:11:01 GMT

In pivot table, you can use slicing/Dicing features,

CustomerName Product Quantity(JAN) Quantity(Feb)so on Price Total Q Total Price

sam aa 12 15

kim ss

and so on

I hope this is what you are looking for.

Just take Month as a Dimension and slice it you would get Month in the Horizontal way;

Total Q: sum(Quantitiy) as Total Q

Total Price: sum(Quantitiy)*sum(Price) as Total Price

Hope this will help

Re: totals

raadwiptec — Mon, 09 Nov 2015 12:28:58 GMT

Hi balraj,

if I do as u mentioned I get the sum for each month ..which is not correct. I need the total.

Re: totals

raadwiptec — Mon, 09 Nov 2015 12:34:05 GMT

for example for the above my total should be 27..

Re: totals

Anonymous — Mon, 09 Nov 2015 12:36:34 GMT

Do you have Month as a DIM?

It shud give you 27 only..

Re: totals

Anonymous — Mon, 09 Nov 2015 12:38:59 GMT

Or you can try:

=sum( Total CustomerName Quantity)

will sum by customer and wud give 27

Re: totals

sasikanth — Mon, 09 Nov 2015 12:49:44 GMT

Please see the attachment

Re: totals

Anonymous — Mon, 09 Nov 2015 12:54:57 GMT

=sum( <Total CustomerName> Quantity) //check the syntax

Re: totals

raadwiptec — Mon, 09 Nov 2015 15:39:42 GMT

no the solution is just select the month and use partial sums.. it is working thanks all