topic Re: Sum and Count at the bottom of the same table. in QlikView

Sum and Count at the bottom of the same table.

rahulsud007 — Mon, 31 Aug 2015 10:23:18 GMT

Dear all

I have requirement in which I need count of items and sum of items in the bottom row as mentioned in the image in a straight or pivot table.

But in Single object not in multiple objects.

Please suggest me.

Re: Sum and Count at the bottom of the same table.

gautik92 — Mon, 31 Aug 2015 10:28:26 GMT

you can select only one option i hope either total or count

Re: Sum and Count at the bottom of the same table.

mightyqlikers — Mon, 31 Aug 2015 10:29:57 GMT

Hi Rahul

create another chart for count with same dimensions and put it under that .

Sam

Re: Sum and Count at the bottom of the same table.

rahulsud007 — Mon, 31 Aug 2015 10:32:49 GMT

Hi gautik92

Thanks for your quick response, unfortunately I need both the fields sum as well as count.

Re: Sum and Count at the bottom of the same table.

rahulsud007 — Mon, 31 Aug 2015 10:33:53 GMT

Hi Sam,

but I need it in single Object and that too in tabular format.

Re: Sum and Count at the bottom of the same table.

gautik92 — Mon, 31 Aug 2015 10:35:32 GMT

not possible

Re: Sum and Count at the bottom of the same table.

rahulsud007 — Mon, 31 Aug 2015 10:51:46 GMT

Is there any alternative, suppose v can do anything in back end to achieve this

Re: Sum and Count at the bottom of the same table.

gautik92 — Mon, 31 Aug 2015 10:54:34 GMT

Unfortunately it is not possible

Re: Sum and Count at the bottom of the same table.

mightyqlikers — Mon, 31 Aug 2015 10:59:55 GMT

i hope you can create another chart

allign it properly bottom of first chart.

Re: Sum and Count at the bottom of the same table.

kuczynska — Mon, 31 Aug 2015 11:03:06 GMT

I think you can try to create (let's call it this way) "synthetic dimension". So field call "Total" in your load script. You can make it look like a default Total row by changing the Text format options in both dimensions and expressions.

The way I prefer is to concatenate a template table with 0 values as all of the dimensions. Have a look at the QVW, maybe it will be of any use to you.

Re: Sum and Count at the bottom of the same table.

sunny_talwar — Mon, 31 Aug 2015 14:52:20 GMT

Are you trying to get this?

For Sum I used the Total on Dimension Limits tab:

For Count I used the Total on the Expression tab and gave it the label on Presentation tab:

Re: Sum and Count at the bottom of the same table.

rahulsud007 — Tue, 01 Sep 2015 04:39:19 GMT

Hi Sunny n Kuczynska, I need count of only the values present greater than 0. !

Re: Sum and Count at the bottom of the same table.

sunny_talwar — Tue, 01 Sep 2015 04:56:18 GMT

This?

Changed the expressions a little bit:

Expression1: =If(Sum([0-1M]) <> 0, Sum([0-1M])) (from Sum([0-1M]))

and so on....

Also changed the Null Symbol from - to 0.000 and made it right justified on presentation tab:

I hope this will help.

Best,

Sunny

Re: Sum and Count at the bottom of the same table.

rahulsud007 — Tue, 01 Sep 2015 05:31:28 GMT

That was very helpful, Sunny.

Is it possible to have a percentage column below count where the formula is total of 0-1M / total inventory for 0-1 M and total of 0-2M/total Inventory for 0-2 n so on..?

Re: Sum and Count at the bottom of the same table.

sunny_talwar — Tue, 01 Sep 2015 05:36:14 GMT

I don't think we can have 3 Total columns, I can recommend another solution where you can have three totals in the one row instead of 3. Do you think that would work???

Re: Sum and Count at the bottom of the same table.

rahulsud007 — Tue, 01 Sep 2015 05:45:41 GMT

Please suggest, Lets me see it works or not

Re: Sum and Count at the bottom of the same table.

rahulsud007 — Tue, 01 Sep 2015 05:49:33 GMT

Sunny !, the third total should take the formula as I have mentioned

Re: Sum and Count at the bottom of the same table.

sunny_talwar — Tue, 01 Sep 2015 05:56:41 GMT

This is the alternative method I was talking about:

Expression1:

=If(Dimensionality() = 0, Num(Sum([0-1M]), '#,##0.000') & Chr(10) &

Num(Count([0-1M]), '#,##0.000') & Chr(10) &

Num(Sum([0-1M])/Sum(TotalInventory), '#,##0.00%'),

Num(Sum([0-1M]), '#,##0.000'))

Output:

Only drawback here is that since we need three totals at the bottom, the complete chart needs to have cell height of 3:

Also attaching the qvw for reference.

HTH

Best,

Sunny

Re: Sum and Count at the bottom of the same table.

rahulsud007 — Tue, 01 Sep 2015 06:51:06 GMT

Hi Sunny,

Thanks a lot for your help.

Your mentioned solution worked well in QV but when we extract to Excel we need to Manually wrap the column.

Is there any Similar solution where we just need to pull the data to excel and it will be in similar format.

Re: Sum and Count at the bottom of the same table.

sunny_talwar — Tue, 01 Sep 2015 07:01:32 GMT

Hey Rahul -

Unfortunately, these are the only two methods I knew to show more than two totals in a table. If there is a third method, I would be more than happy to know about it.

I hope someone can help you out with this.

Best,

Sunny