topic Re: Straight table vs table box in QlikView

Straight table vs table box

reshmakala — Thu, 16 Jul 2015 14:28:18 GMT

Hi all,

I have to show few transactions as a straight table with totals.

my data is as below:

Institute Year Amt1 Amt2 Amt3 Amt4

ABCD 2015 0.00 85.43 1000.23 32423.32

ABCD 2015 0.00 0.00 1098.43 23.32

I do not want to sum these up but I need totals at the top. When I show this data in straight table by putting these amount field names as expressions nothing comes up. When I use a table box I can see detail transactions but I am not able to show a total there? Any help why this problem with Straight table? Please find my test application attached.

Thanks in advance.

Re: Straight table vs table box

alexandros17 — Thu, 16 Jul 2015 14:32:50 GMT

Use Institute and year as dimension,

Amt1 Amt2 Amt3 Amt4 as expression but using

Sum(Amt1) for expr.1

Sum(Amt2) for expr.2

....

Re: Straight table vs table box

swuehl — Thu, 16 Jul 2015 14:34:11 GMT

You need to use an aggregation function in your chart expressions.

Something like

=sum(amt2)

Use Aggregation Functions!

Re: Straight table vs table box

sunny_talwar — Thu, 16 Jul 2015 14:35:22 GMT

This?

If yes then PFA

Best,

Sunny

Re: Straight table vs table box

reshmakala — Thu, 16 Jul 2015 14:39:38 GMT

Thank you for the replies!

I do not want to sum them up. I need to show every transaction as it looks in the table box with totals. Is this possible with a straight table?

Re: Straight table vs table box

sunny_talwar — Thu, 16 Jul 2015 14:42:04 GMT

This?

Re: Straight table vs table box

phaneendra_kunc — Thu, 16 Jul 2015 14:43:00 GMT

Use attached example. AGGR will be expensive to calculate on large data sets. If it is Straight table use the logic of hiding dimensions.

Re: Straight table vs table box

reshmakala — Thu, 16 Jul 2015 14:43:31 GMT

Thank you.But I would need totals too

Re: Straight table vs table box

swuehl — Thu, 16 Jul 2015 14:43:53 GMT

The table box will not show every transaction, just unique combinations of your fields.

If you want to show every transaction, add a key to your table:

LOAD Institute,

Year,

amt1,

amt2,

amt3,

amt4,

recno() as TransID

FROM

MAS.xls

(biff, embedded labels, table is [Sheet 1$]);

Add this key to your table chart (as dimension) / table box and for the table chart, use sum(FIELD) as expression.

Re: Straight table vs table box

petter — Thu, 16 Jul 2015 14:44:44 GMT

Sum of a single number is the number without a sum - so what is the problem? For you to get a total you need to specify a sum.

It is possible to write the field name without a sum but then you have to write an if to check whether the row you are on is the total (sum) row and then perform a sum anyway... The approach Alessandro is the best in fact.

You could however write:

If ( RowNo() = 0 , Sum( Amt1 ) , Amt1 )

If the Institute and Year doesnt give you a single value then this formula will give a null in the corresponding row...

Re: Straight table vs table box

kuczynska — Thu, 16 Jul 2015 14:44:55 GMT

Hi Reshma,

Probably you did check that already, but I think straight table always puts the Totals into the last row by default (or maybe pivot table does? not sure). You can change this option by going to Presentation tab:

Second thing you might want to check is to make sure that the Total option has been enabled in the first place - go to Expressions tab and check the below:

If the above seem to be ok in your case - well, we can have a look again at your expressions.

Michalina

Re: Straight table vs table box

reshmakala — Thu, 16 Jul 2015 14:46:26 GMT

Thank you! This helps me!