topic Re: columns in QlikView

columns

raadwiptec — Mon, 09 Nov 2015 12:38:56 GMT

hi all,

is there any way to add columns and get the total as in red

example as below. in red

the price is an expression.. not a dimension...

Jan	feb	mar
product	price	price	price	total
a	10	10	10	30
b	20	20	20	60

Re: columns

sunny_talwar — Mon, 09 Nov 2015 12:40:25 GMT

You can do something like this:

=RangeSum(FirstExpression, SecondExpression, ThirdExpression)

Re: columns

Peter_Cammaert — Mon, 09 Nov 2015 12:40:56 GMT

I guess you are using a pivot table with a Product dimension on the left and a Month dimension at the top. Add subtotals for the Month dimension and you'll get your total column.

Re: columns

Anonymous — Mon, 09 Nov 2015 12:42:14 GMT

try:

=sum(<total Product> Price)

Re: columns

raadwiptec — Mon, 09 Nov 2015 12:43:21 GMT

hi peter ,

yes iam using a pivot table

Re: columns

raadwiptec — Mon, 09 Nov 2015 12:44:54 GMT

hi sunny,

when I trying to use this iam getting every month the sum which is not correct..

Re: columns

raadwiptec — Mon, 09 Nov 2015 12:46:09 GMT

at the end I should have one total column.. i.e for product a - total price is 30

Re: columns

raadwiptec — Mon, 09 Nov 2015 12:48:38 GMT

hi peter ,

if I do as u mentioned. I get the total price of all the products for jan month.. which is still not correct.

it should be only column adding up to a total for a single product

Re: columns

sunny_talwar — Mon, 09 Nov 2015 12:50:07 GMT

Can you share a sample?

Re: columns

Peter_Cammaert — Mon, 09 Nov 2015 12:51:31 GMT

Switch to a subtotal for the other dimension and see what happens.

Don't hesitate to experiment in QlikView. You never know what great new tricks you'll discover.

Re: columns

nagarjuna_kotha — Mon, 09 Nov 2015 12:52:23 GMT

Hi Raadwiptec,

In Expression Tab of your chat.Expand your total expresion >>In Text Colour .Put >>> =Red()

Re: columns

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

Don't hesitate to experiment in QlikView. You never know what great new tricks you'll discover.

A very useful advice Peter‌ . I love experimenting because the worst that can happen is that the experiment won't work, but the best things is the satisfaction I get when it actually does work after 15-20 trials.

Re: columns

Peter_Cammaert — Mon, 09 Nov 2015 12:57:19 GMT

I'm getting the expected result if I enable Partial Sums for Dimension Month:

Re: columns

nagarjuna_kotha — Mon, 09 Nov 2015 13:00:38 GMT

Like this you want ?

PFA....

Re: columns

raadwiptec — Mon, 09 Nov 2015 14:05:58 GMT

Hi Peter,

This is perfect. Iam getting the desired results. But iam not able to move the Total colums to the end...

Re: columns

rubenmarin — Mon, 09 Nov 2015 14:14:15 GMT

Hi, about the colour in the expressions tab, expand the expression to set color to lightred:

And about the totals, in presentation tab, check subtotals down.

Re: columns

Peter_Cammaert — Mon, 09 Nov 2015 14:23:05 GMT

In the Object Properties->Presentation tab, select the correct subtotals position in the Subtotals group (middle right). There are two values that correspond to positions for vertical dimensions (totals at the top or the bottom). If you turned your dimension into a horizontal one, top becomes LEFT and bottom becomes RIGHT.

Select the bottom position for your Month dimension. Your totals column will then show up on the right end.

Note that if you have monre dimensions stacked on top of each other, the subtotals will be repeated for every value of a higher dimension.

Peter

Re: columns

raadwiptec — Mon, 09 Nov 2015 15:38:18 GMT

yes the solution is put the totals down and do as peter mentioned for totals.

Thanks all