topic Drill down table detail in QlikView

Drill down table detail

Mon, 21 Dec 2009 22:54:44 GMT

Hi, I am having a problem using drill down tables, I have two tables using a drill down format the structure of the drill down is this:

1st level, Cost centre,

2nd level grouping

3rd level object account

4th level transaction detail

What happens is you chose the cost centre you are interested in, then the group and object account. Then when you select the object account a different straight table comes to the front using the same structure to provide you with the transaction detail (this is because I wanted to use completely different fields at this level). My problem is this, when you reach this level it doesn't show all of the transactions, instead it sums them. I cannot work out how to stop this. Any ideas how? I know the data is there because if I use a normal table then you can see all of the transactions. However I want the structure of the drill down for ease of use! Help!

Thanks

Ben

Drill down table detail

Tue, 22 Dec 2009 00:52:06 GMT

Ben,

Could you upload a sample or scrambled qvw so we can have a look?

Drill down table detail

Tue, 22 Dec 2009 15:59:22 GMT

Hi Jason, please find a reduced scrambled version of the QVW attached, it should provide enough of an idea as to the problem, if you select cost centre 191 then object accound 1420 you get the list of transactions, I have put in a temporary table to illustrate the problem. When you have drilled down there are infact some 5000+ transactions that are all shown in the temporary table, however the drill down table sums these and so only 80+ appear. How can I stop this happening?

Cheers

Ben

Drill down table detail

johnw — Wed, 23 Dec 2009 02:25:48 GMT

QlikView charts (such as a pivot table) group by the dimension field(s). To see individual transactions in a chart, you must give each transaction a unique transaction ID, and use that transaction ID as your dimension or one of your dimensions. If you don't want to SEE the transaction ID, you could change it to a straight table and hide that dimension. I'm fairly confident that charts still group by hidden dimensions. Or you could just use a table box instead of a pivot/straight table, since as you've already observed, a table box does NOT do any grouping. However, I would think you'd want to see the transaction ID, even if only to be able to go back to your real database to look things up when you see suspicious data.

Drill down table detail

rwunderlich — Wed, 23 Dec 2009 02:37:35 GMT

A Straight chart will produce one line for each unique combination of dimension values. You have only a single dimension. For example, there are many GL transactions for Document No 2945799 but this will be represented by one line line in the straight chart.

The remaining attributes, such as GL Amount, you've defined as Expressions, which means they will be aggregated by dimension. However, you've negelected to include an aggregation expression, so they will show null. For example instead of just [GL Amount], use -Sum([GL Amount]).

If you want a seperate line to appear for each combination of attributes, make them dimensions in the table instead of expressions. If you don't want any aggregation, use a table box instead.

-Rob